RJDBC can't read table from MSSQL server

Question

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?

Answer 1

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) 

Answer 2

Give the exact name of the table that includes database name , if exist dbo and table name for example :

queryResults <- dbGetQuery(conn, "SELECT * FROM [dbname].[dbo].[tablename]")