Search code examples
sql-serverrmssql-jdbcrjdbc

RJDBC can't read table from MSSQL server

I'm running R locally and the database sits on a MS SQL server remotely.

I do my prep work: 

require(RJDBC)
# initialize the driver 
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "C:/files/sqljdbc4.jar") 
# establish the connection 
conn <- dbConnect(drv, "jdbc:sqlserver://DBserverName.example.com;instanceName=myINS;databaseName=myDB", "myUser", "myPass")

Then I check if I can read the header of some tables: 

dbListFields(conn,"table1")

Works. Next I try to read the full table: 

dbReadTable(conn,"table1")

Fails with: 

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for SELECT * FROM table1 (Invalid object name 'table1'.)

Doing it the SQL way also fails: 

sqlText <- paste("SELECT FROM \"table1\" ")
queryResults <- dbGetQuery(conn, sqlText)

Fails with: 

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for SELECT * FROM "table1"  (Invalid object name 'table1'.)

and without quotes: 

sqlText <- paste("SELECT FROM table1 ")
queryResults <- dbGetQuery(conn, sqlText)

Fails with: 

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for SELECT * FROM table1  (Invalid object name 'table1'.)

Any idea why this fails?

Solution

  • To actually read from the table it needs to be specified with its hierarchical prefix. The following worked: 

    dbReadTable(conn,"groupingA.table1")

    Likewise works: 

    sqlText <- paste("SELECT FROM groupingA.table1 ")
queryResults <- dbGetQuery(conn, sqlText)