SQL xp_cmdshell not recognising R library package

Question

I cannot seem to get SQL xp_cmdshell to run an R script that includes loading a library.

Am using Windows 10, MSSQL v15, R4.1

What I am trying to do is run an R script that has several libraries that load. It is running fine in RStudio and via rscript.exe but I cannot run externally from SQL

(NOTE: I do not want to setup the R scripts with SQL RServices (sp_execute_external_script); the version of R scripts we are coding requires R v4.1 code/libraries and SQL only supports 3.5. So I just want to call rscript.exe from MSSQL procedure or Agent. We do this with things like calling ogr2ogr.exe from a BAT file)

1. so to test I made a very simple Script is the file RforCmd.r (note it does not use "janitor", simply a test to see if it will load):

#### load libs
library("janitor")
#### do some calc
a <- 1
b <- 2
x <- a/b
y <- a*b
print(c(x, y))

2. my batch file is myBatch.bat

REM use rscript.exe to run the R file
D: cd D:\temp
c:\R\R-4.1.0\bin\x64\rscript.exe d:\temp\RforCmd.r

3. in SQL I am executing:

exec master..xp_cmdshell 'd:\temp\myBatch.bat'

but that throws an error in the output :

C:\WINDOWS\system32>D: d:\>cd d:\temp 
d:\temp>c:\R\R-4.1.0\bin\x64\rscript.exe d:\temp\RforCmd.r    
Error in library("janitor"): there is no package called 'janitor'       
Execution halted

I have got in my Windows System Environment Variable PATH the directories for R:

C:\R\R-4.1.0\bin\
C:\Users\luke\Documents\R\win-library\4.1\

The BAT that runs rscript.exe works fine from a Windows CMD window (e.g. 'janitor' package is installed on this machine):

D:\temp>d:\temp\myBatch.bat

D:\temp>d:
D:\temp>cd d:\temp
D:\temp>D:\_R\R-4.1.0\bin\x64\rscript.exe d:\temp\RforCmd.r
Attaching package: 'janitor'
The following objects are masked from 'package:stats':
    chisq.test, fisher.test

[1] 0.5 2.0

D:\temp>

It also works fine from RStudio console:

source("D:/temp/RforCmd.r", echo=TRUE)
#### load libs
library("janitor")
#### do some calc
a <- 1
b <- 2
x <- a/b
y <- a*b
print(c(x, y))
[1] 0.5 2.0

What I have tried

1. if I try instead to use SQLAgent and use the job step Operating System (CmdExec) then I get the same error as exec master..xp_cmdshell

2. If in the RforCmd.r I rem out library("janitor") then the xp_cmdshell or SQLAgent works, it does the calc, but of course no library loaded, which we need it to (e.g. not a permission problem in SQL running the proc or SQLAgent)

3. if in the RforCmd.r I specify the lib path:

library.path <- .libPaths()
library("janitor", lib.loc = library.path)
/#### do some calc
a <- 1
b <- 2
x <- a/b
y <- a*b
print(c(x, y))

then xp_cmdshell or SQLAgent still throw an error:

Error in library("janitor") : there is no package called 'janitor'

(works fine in CMD window or RStudio console)