dbReadTable error in R: invalid object name

Question

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'.

I cannot reproduce this. I'm currently using odbc-1.1.6 with DBI-1.0.0 on R-3.5.1 (x86_64-w64-mingw32). My server is Microsoft SQL Server 2017 (RTM-CU9-GDR) (KB4293805) - 14.0.3035.2 (X64) (from select @@version). The ODBC driver I'm using is "SQL Server" (b/c the "newer" drivers are broken in new ways). If you have version differences, that may be indicating where the problems lie. If so, this might be worthy of a bug-report to the particular package (or you and I need to determine what other changes the new version brings). — r2evans
– r2evans, Commented Sep 6, 2018 at 21:38
I believe my SQL Server is 2012. I'll check my other packages when I get back to work. Thanks for your support with this. — Nova
– Nova, Commented Sep 7, 2018 at 2:44

Sturgus · Accepted Answer · 2018-09-10 14:05:03Z

4

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.

answered Sep 10, 2018 at 14:05

Sturgus

6864 silver badges18 bronze badges

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

1 Comment

Nova Over a year ago

Thanks @Sturgus! @r2evans, I was able to then load the table by calling samptable<-dbGetQuery(con, "SELECT * FROM AdminOnly.tlkpSampleStatus").

Richard van der Pool · Accepted Answer · 2019-12-02 15:05:04Z

1

The dbplyr package lets you select which schema to use with the in_schema function. That should do the trick.

library(dbplyr)
con_tlkpSampleStatus <- tbl(con, in_schema("abc", "tlkpSampleStatus"))

answered Dec 2, 2019 at 15:05

Richard van der Pool

111 bronze badge

Comments

franiis · Accepted Answer · 2018-09-07 10:02:02Z

0

Same problem here. I did find out that it has to do with the schema of the table. You can list schema by first setting up a channel:

ch = odbcDriverConnect(DSM, Uid, Pwd)

Then run:

tables = sqlTables(ch)

I found out that I can read in tables that have the schema dbo, but not sys.

edited Sep 7, 2018 at 10:02

franiis

1,3761 gold badge19 silver badges33 bronze badges

answered Sep 7, 2018 at 8:07

Vincent Mullers

611 silver badge4 bronze badges

6 Comments

r2evans Over a year ago

I cannot find sqlTables in either DBI or odbc; it is exported by RODBC, which is different than the OP's environment. Are you suggesting that you connect to a MS SQL Server using RODBC and (also) do not appear to have the OP's problem?

Vincent Mullers Over a year ago

True, I did not manage to display the table schemas by leveraging any of the functions within DBI or odbc, so I set up a connection through RODBC and ran sqlTables. I just now solved the problem by changing the schema of the table I wanted to retrieve to dbo. I can now import the table without any errors. I'm guessing "traptable" has schema dbo but "tlkpSampleType" not.

r2evans Over a year ago

Just as it is important to provide a reproducible question, it is even more important for the suggested solution to be a self-contained answer. In this case, it is important to be very clear and state that you are completely changing the connection methodology. There are about as many differences as similarities between DBI-based packages and RODBC to be a not-so-trivial change. Your comment about not being within the dbo schema is possibly where the solution will be found, perhaps Nova will find a way to determine that.

Vincent Mullers Over a year ago

Just to be clear, it works for both DBI and RODBC connections I set up. In this case, I merely set up the RODBC connection to check out the schema of the tables in the database. I did not find a function within the DBI package that displays the schema for each table, so for this had to resort to RODBC. Anyways, I hope having a look at the schemas will help Nova to determine the issue.

r2evans Over a year ago

Ok, it wasn't immediately clear to me that you confirmed with DBI that tables in a different schema were not listed, and they were listed when tried with RODBC, I see that now. A mssql-specific method for determining the table schema is with select * from INFORMATION_SCHEMA.COLUMNS, and I mentioned this in the OP's previous question (which you may not have known about).

|

Collectives™ on Stack Overflow

dbReadTable error in R: invalid object name

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related