I'm trying to load a very large dataset into R that is stored in SQL. I am able to use local R (Microsoft R Client 3.3.3.0) to connect to SQL via the following:
library(RODBC)
db <- odbcDriverConnect('driver={SQL Server};server=servername\\servername;database=dbname;trusted_connection=true')
tb <- paste("select top 100 * from dbname", sep = "")
df <- sqlQuery(db, tb)
And all that works fine. However, when I try to do this in a remote session (MMLS R version 3.4.3), it doesn't work:
library(mrsdeploy)
remoteLogin("http://some.url", session = TRUE)
REMOTE> #insert script from above
which returns the following error message:
Error in sqlQuery(db, tb) : first argument is not an open RODBC channel
The problem is with odbcDriverConnect(), as running that locally returns, as expected, an object of class "RODBC" delineating the RODBC connection details but running that remotely returns a scalar of class "integer" (-1).
Am I doing something wrong or is it not possible to remotely connect to a SQL database while remotely connected to an R server?