1

What I'm trying to do:

  • I have a long script I wrote in RStudio which links several SQL databases to pull information and ends up putting it all in to a table in one of these databases.
  • I want to make this automated, so I have a computer that is on all day everyday that I have created a task scheduler daily task for to automatically open R.exe and run this script.
  • The script works perfectly in RStudio, but has errors when run in R.exe.
  • The location I am using to automatically run the script via R.exe is ‪C:\Program Files\R\R-4.0.4\bin\x64\R.exe
  • Here is a list of packages and initial setup I am using:
rm(list = ls())

install.packages("RDCOMClient", repos = "http://www.omegahat.net/R")
install.packages('xlsx', repos = "https://mirror.las.iastate.edu/CRAN/")
install.packages('RODBC', repos = "https://mirror.las.iastate.edu/CRAN/")
install.packages('dplyr', repos = "https://mirror.las.iastate.edu/CRAN/")
install.packages('devtools', repos = "https://mirror.las.iastate.edu/CRAN/")
install.packages('usethis', repos = "https://mirror.las.iastate.edu/CRAN/")


library(xlsx)
library(RODBC)
library(RDCOMClient)
library(dplyr)
library(devtools)

connClarity <-odbcConnect("Clarity")
connMCIF <-odbcConnect("MCIF")

Following this is a long code that I'll only post if needed, but the code works just fine in RStudio.

Here is the first and main error that I'm getting:

SaveToMCIF <- sqlSave(connMCIF, CovidTestOrders, tablename = "Import_tblCOVI$
Error in sqlSave(connMCIF, CovidTestOrders, tablename = "Import_tblCOVID19_Events_Information",  :
  should be a data frame

For some reason, it thinks the dataframe CovidTestOrders isn't a dataframe. I used is.data.frame to check, and in RStudio it says TRUE, but in R.exe it says FALSE.

Here is the start of the code used to make CovidTestOrders:

CovidTestOrders <- sqlQuery(connClarity,"


WITH COVIDORDER AS (
SELECT     CASE
                        WHEN CLOC1.LOC_NAME IS NOT NULL AND CBED.BED_LABEL IS NULL THEN ' '
                        WHEN CLOC1.LOC_NAME IS NULL THEN ' '
                        ELSE CLOC1.LOC_NAME
                    END AS HOSPITAL 
                   ,CASE
                        WHEN CDEP1.DEPARTMENT_NAME IS NULL THEN ' '
                        ELSE CDEP1.DEPARTMENT_NAME

I tried forcing CovidTestOrders to be a dataframe by starting with data.frame(), which indeed made it a dataframe however it put all 28 columns in to 1 column. The version of R.exe I'm using is 4.0.4, but I have older versions that I've tried as well (also tried bin i386 vs x64) and they either didn't work or had different errors.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Jordan Hersey
  • 27
  • 6
  • Are you using a 32-bit version of RStudio? The DSNs listed in ODBC Data Sources are separate from those listed in ODBC Data Sources (64-bit). – AlwaysLearning Apr 30 '21 at 08:59
  • For RStudio, I'm using v1.2.5033 and it is the x64 bit. So is the ODBC Data Source different when running RStudio versus just running an R Script with the .exe? – Jordan Hersey Apr 30 '21 at 13:48
  • All 32-bit processes share the same DSNs in ODBC Data Sources. All 64-bit processes share the same DSNs in ODBC Data Sources (64-bit). But they aren't shared between 32-bit and 64-bit processes, if that makes sense. – AlwaysLearning Apr 30 '21 at 13:58
  • Is the `Clarity` DSN using a username and password (SQL Login authentication) or Windows authentication? If it's the latter then it could be behaving differently if the scheduled task executes R.exe with a different Windows account than what you use for R Studio. – AlwaysLearning Apr 30 '21 at 14:00
  • I believe both the CLARITY and MCIF DSN are "Windows NT Authentication using the network login ID", or at least that's how it's set up that way when using SQL Server. Would how I'm connected to these databases affect the fact my first chunk of code in .exe `character` rather than a `dataframe`? – Jordan Hersey Apr 30 '21 at 14:30
  • 1
    Different logins (Windows accounts in this case) could be mapped to different databases, different database users and hence have a different set of grant/deny permissions. You'd need to audit the permissions between your own account and the scheduled task's account to see. It's possible that the "should be a data frame" is being raised because `CovidTestOrders` is a `NULL` value and its [`sqlQuery`](https://www.rdocumentation.org/packages/RODBC/versions/1.3-17/topics/sqlQuery) isn't raising the original error correctly. – AlwaysLearning Apr 30 '21 at 14:36

0 Answers0