I have an R script that works perfectly from within R and R Studio. It includes use of the RODBC package, in which I am connecting to an Oracle db to get some data.
I wrote the whole thing from within R Studio, where it works flawlessly. I can also run it with no problems directly from the R console by typing:
source("C:/Users/yadda.yadda/script.R")
This means that it's able to get all my connection information and connect to Oracle via RODBC without anything from me.
The next step I wanted to do was to set it to run at regular intervals as a scheduled task. To do so, I wrote a tiny bat file with the contents:
C:\R\R-2.15.2\bin\Rscript.exe "C:\Users\yadda.yadda\script.R"
PAUSE
When I run the bat file, it prints out the initial "print" statements from within my script, but fails with "first argument is not an open RODBC channel". The full output is as follows:
C:\Users\yadda.yadda\schedule>C:\R\R-2.15.2\bin\Rscript.exe "C:\Users\yadda.yadda\script.R"
[1]
Housekeeping
Loading required files
Loading the function
Calling the function...
Loading required package: RODBC
Error in sqlQuery(channelMflib, the.SQL) :
first argument is not an open RODBC channel
Calls: get.all.areas -> oceans.get.data -> sqlQuery
Execution halted
C:\Users\yadda.yadda\schedule>PAUSE
Press any key to continue . . .
I have been googling pretty hard, and can't figure it out. I am able to run a simpler R script that does not include the RODBC connection, so I know that the bat file syntax is fine.
If I can't solve it, I will look into trying it with ROracle (instead of RODBC), but as nobody else around here uses ROracle, it won't be nearly so flexible/exchangeable with my coworkers.
Thanks for any insight! Mike
EDIT
nograpes asked for a barebones version of script.R. I've created one, but in doing so, have perhaps a slightly more informative error message.
script.R is:
print("starting")
require(RODBC)
print("loaded RODBC")
channel<-odbcConnect(thedsn,username,password)
the.SQL <-paste("SELECT * from dual;",sep="")
df<-sqlQuery(channel,the.SQL)
print("done, data to follow")
print(df)
output using the above is:
[1] "starting"
Loading required package: RODBC
Warning message:
package 'RODBC' was built under R version 2.15.3
[1] "loaded RODBC"
Warning messages:
1: In odbcDriverConnect("DSN=thedsn;UID=username;PWD=password") :
[RODBC] ERROR: state IM014, code 0, message [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
2: In odbcDriverConnect("DSN=thedsn;UID=username;PWD=password") :
ODBC connection failed
Error in sqlQuery(channel, the.SQL) :
first argument is not an open RODBC channel
Execution halted
Obviously, I've changed the username and password, but I know the ones I use work, and have been able to have the script print out what it's using and confirm they're OK.
I had not seen the "architecture mismatch" before, so I'll google that too. For the record, I've been using 64 bit R - I wonder if I can force it to use 32 bit to see if that makes a difference I think I've been using 32 bit R, so I'll see if I can force it to use 64 bit - I seem to remember reading that the mismatch error relates to the 32/64bit usage of RODBC. I'll google that too. Thanks for reading.
script.R? Try cutting it down to only the essential parts (so it still fails, but has very few lines).