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