Search code examples
rsql-serverr-dbi

dbReadTable error in R: invalid object name

I've been scouring stackoverflow and google for an hour trying to figure out why my table won't show up. So far solutions that have worked for others, don't work for me.

I'm connecting to a database like this:

library(DBI)
library(dplyr)
con <- dbConnect(odbc::odbc(), .connection_string = "Driver={SQL Server};", 
                 user = rstudioapi::askForPassword("Your computer usename e.g. SmithJo"), 
                 password = rstudioapi::askForPassword("Your computer password (e.g. to login to Windows)"), 
                 port = 1433, 
                server = "myserver", 
                dbname = "MYDB")

Connecting to some tables works:

traptable <- dbReadTable(con, "tblTrap")

And others don't, even though the table exists! 

> dbExistsTable(con, "tlkpSampleType")
[1] TRUE

I've included some of the solutions I've tried...

> dbReadTable(con, "tlkpSampleType")
Error: <SQL> 'SELECT * FROM "tlkpSampleType"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleType'. 
> dbReadTable(con, SQL("tlkpSampleType"))
Error: <SQL> 'SELECT * FROM tlkpSampleType'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleType'. 
> dbGetQuery(con, "SELECT * FROM tlkpSampleStatus")
Error: <SQL> 'SELECT * FROM tlkpSampleStatus'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus'. 
> dbReadTable(con,"MYDB.tlkpSampleStatus")
Error: <SQL> 'SELECT * FROM "MYDB.tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDB.tlkpSampleStatus'. 
> queryResults <- dbGetQuery(con, "SELECT * FROM [MYDB].[abc].[tlkpSampleStatus]")
Error: <SQL> 'SELECT * FROM [MYDB].[abc].[tlkpSampleStatus]'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDB.abc.tlkpSampleStatus'. 
> dbReadTable(con, Id(schema = "tlkpSampleStatus"))
Error: <SQL> 'SELECT * FROM "tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus'. 
> dbReadTable(con, Id(schema = "MYDB", table = "tlkpSampleStatus"))
Error: <SQL> 'SELECT * FROM "MYDB"."tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MYDB.tlkpSampleStatus'. 
> dbGetQuery(con, paste('SELECT * FROM', 'tlkpSampleStatus'))
Error: <SQL> 'SELECT * FROM tlkpSampleStatus'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus'. 
> dbReadTable(con, Id(schema = "tlkpSampleStatus", table = "tlkpSampleStatus"))
Error: <SQL> 'SELECT * FROM "tlkpSampleStatus"."tlkpSampleStatus"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlkpSampleStatus.tlkpSampleStatus'.
Solution

  • It's not uncommon for lookup tables to be kept in a different schema than storage tables. Either way, you should qualify your table names with their schema names when you can. E.g.,

    >dbReadTable(con, SQL("dbo.tlkpSampleType"))

    To see the complete list of tables and their schemas, you could do something like:

    1  rs <- dbSendQuery(con, "SELECT SCHEMA_NAME(schema_id) As SchemaName, name As TableName FROM sys.tables;")
2  dbFetch(rs)

    From there, you can qualify the tables appropriately for future queries.