3

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'.
ARandomUser
  • 130
  • 12
Nova
  • 5,423
  • 2
  • 42
  • 62
  • 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 Sep 06 '18 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 Sep 07 '18 at 02:44

3 Answers3

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.

Sturgus
  • 666
  • 4
  • 18
  • Thanks @Sturgus! @r2evans, I was able to then load the table by calling `samptable<-dbGetQuery(con, "SELECT * FROM AdminOnly.tlkpSampleStatus")`. – Nova Sep 10 '18 at 14:09
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"))
Richard van der Pool
  • 11
  • 1
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.

franiis
  • 1,378
  • 1
  • 18
  • 33
Vincent Mullers
  • 51
  • 1
  • 4
  • 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? – r2evans Sep 07 '18 at 11:08
  • 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. – Vincent Mullers Sep 07 '18 at 12:27
  • Just as it is important to provide a [reproducible question](https://stackoverflow.com/questions/5963269/), 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. – r2evans Sep 07 '18 at 12:40
  • 1
    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. – Vincent Mullers Sep 07 '18 at 13:14
  • 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](https://stackoverflow.com/questions/52208380/connect-to-a-single-sql-server-database-with-dbconnect-in-r?noredirect=1#comment91366846_52208380) (which you may not have known about). – r2evans Sep 07 '18 at 14:02
  • Thanks all. My colleague who maintains the database is out for the day so I hope to ask him about this on Monday. I will let you know what I find out. – Nova Sep 07 '18 at 18:20