Invalid object name Error when querying SQL Server Database from R using ODBC

Question

I have setup a connection in R:

conn <- dbConnect(odbc::odbc(),
                 Driver = "SQL Server",
                 Server = "...",
                 Database = "AdventureWorks2012")

The default ODBC driver is AdventureWorks2012. I can see the data set just fine and for example, the following code runs without a problem:

dbGetQuery(conn, "SELECT TOP 10 * FROM Person.Person")

On the other hand, running

dbReadTable(conn, "Person.Person")

yields:

Error: <SQL> 'SELECT * FROM "Person.Person"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Person.Person'.

Similary, running

data <- tbl(conn, "Person.Person")

yields:

Error: <SQL> 'SELECT *
FROM "Person.Person" AS "zzz12"
WHERE (0 = 1)'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Person.Person'.

Also, I am stumped as to WHERE (0 = 1) : cannot figure out where it comes from!!!

(1) The table does exist in the database. (2) I have qualified the name with the correct Schema. (3) I have set the default database in ODBC to AdventureWorks2012.

I would appreciate your help and insight. Thank You ...

> tableList <- dbListTables(conn)
> tableList
  [1] "AWBuildVersion"                                      
  [2] "DatabaseLog"                                         
  [3] "DeleteExample"                                       
  [4] "EmployeeSales"                                       
  [5] "ErrorLog"                                            
  [6] "PersonCross"                                         
  [7] "PhoneCross"                                          
  [8] "Department"                                          
  [9] "Employee"                                            
 [10] "EmployeeDepartmentHistory"                           
 [11] "EmployeePayHistory"                                  
 [12] "JobCandidate"                                        
 [13] "Shift"                                               
 [14] "Address"                                             
 [15] "AddressType"                                         
 [16] "BusinessEntity"                                      
 [17] "BusinessEntityAddress"                               
 [18] "BusinessEntityContact"                               
 [19] "ContactType"                                         
 [20] "CountryRegion"                                       
 [21] "EmailAddress"                                        
 [22] "Password"                                            
 [23] "Person"                                              
 [24] "PersonPhone"
 ...

> dbReadTable(conn, "Person")
Error: <SQL> 'SELECT * FROM "Person"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Person'.

I don't know much about odbc stuff, but that 'SELECT * FROM "Person.Person"' stuff seems strange. I don't think "-quotes should be used for an object's name, maybe try dbReadTable(conn, Person.Person)? — George Menoutis
– George Menoutis, Commented Apr 4, 2018 at 19:53
According to AdventureWorks, there is no Person.Person table. There is Person.Contact and Sales.SalesPerson. — Parfait
– Parfait, Commented Apr 4, 2018 at 21:10

epi_n00b · Accepted Answer · 2019-01-08 19:16:51Z

3

I came across this same problem and discovered an answer in the dbplyr documentation:

https://db.rstudio.com/best-practices/schema/ where it includes this error message and says you must do: tbl(con, in_schema("production", "flights"))

However, "production" here is specific to your database.

It took me a while to figure out what my schema name was, but it turns out it was just the name of the database. So, maybe you could try

    conn <- dbConnect(odbc::odbc(),
             Driver = "SQL Server",
             Server = "...",
             Database = "AdventureWorks2012")
    data <- tbl(conn, in_schema("AdventureWorks2012", "Person"))

or

    data <- tbl(conn, in_schema("Person", "Person"))

?

answered Jan 8, 2019 at 19:16

epi_n00b

1503 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2018-04-04 21:10:32Z

1

Since AdventureWorks maintains no default schema (i.e., no dbo), consider using odbc::dbId to properly select the table under such a schema as shown in this Git issue comment:

conn <- dbConnect(odbc::odbc(),
                  Driver = "SQL Server",
                  Server = "...",
                  Database = "AdventureWorks2012")

tbl <- dbId(conn, "Contact", "Person")
dbReadTable(conn, tbl)

NOTE: Support for schemas is relatively new for odbc package per one of the authors as recently as 15 days ago. You may have to re-install a Git development version if CRAN version is too old.

answered Apr 4, 2018 at 21:10

Parfait

108k19 gold badges103 silver badges138 bronze badges

3 Comments

Sasha Babaei Over a year ago

Thank you. The dbId method is not still in the stable master branch. I would rather not utilise that.

Parfait Over a year ago

Understood. Curious, what happens when you don't put any period qualifying schema but just query the table name? Also, what does dbListTables(conn) show?

Parfait Over a year ago

Please edit your post to show output of dbListTable(conn). Can you not use exact name in a SELECT query?

zola25 · Accepted Answer · 2019-04-16 12:05:57Z

0

I found I was getting this error when I was using a connection string with an 'Initial Catalogue=dbname' setting, rather than 'Database=dbname':

# This works
connectionString <- "Driver={ODBC Driver 17 for SQL Server};Server=tcp:dbServer,1433;Database=dbName;Persist Security Info=False;UID=dbUser;PWD=dbPassword;MultipleActiveResultSets=False;Connection Timeout=30;"

conn<-dbConnect(odbc::odbc(), .connection_string = connectionString)
dbReadTable(conn, "Person")

answered Apr 16, 2019 at 12:05

zola25

1,9416 gold badges27 silver badges46 bronze badges

Collectives™ on Stack Overflow

Invalid object name Error when querying SQL Server Database from R using ODBC

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related