0

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?

coip
  • 1,312
  • 16
  • 30
  • If odbcDriverConnect fails it returns -1 and not an RODBC. In the docs the return value is: `A non-negative integer which is used as handle if no error occurred, -1 otherwise. A successful return has class "RODBC", and attributes including ...` You need to find out why the remote can't connect . – Brian Jan 07 '21 at 20:59
  • Thank you. So that confirms the RODBC connection fails when initiated from R Server. But it works just fine when initiated from local R. Do I need to do something differently to make it work from a remote R connection? Or is it only possible to open an RODBC channel to a SQL server from an R client running locally? – coip Jan 07 '21 at 21:03
  • @coip Do you have all of the appropriate drivers installed on the remote server? Are the drivers available to your user on the server? You should be able to connect without issue, provided everything is available to make the connections. – Steven Jan 08 '21 at 00:39
  • Is the SQL server listening on IP address(es) that are visible to the remote client? Does Windows Firewall permit traffic on that port from the remote client? Is the SQL Browser service running? Does Windows Firewall permit traffic on udp/1434 from the remote client? `trusted_connection=true` ... are SQL Server and the remote client authenticated by the same Active Directory? – AlwaysLearning Jan 08 '21 at 04:38
  • Thank you both for those comments. I will look into those possibilities and see if that fixes thing. Slow-moving organization, though, so it may take a few days to check. Will let you know. – coip Jan 08 '21 at 14:56

0 Answers0