Connect to a DB using DBplyr

Problem

I am trying to connect to a SQL Server which is on premises using dbplyr using the instructions here but I keep getting an error saying

Invalid object name

For simplicity I am using the same object naming convention as RStudio has in the instructions at the link above.

Code that produces the error

I use dbConnect to establish a connection:

con <- dbConnect(odbc::odbc(),
             .connection_string = "Driver={SQL Server};server=DO1COMMONSQL01\COMMON1D;database=datawarehouse;trusted_connection=Yes",
             timeout = 10)

I know has been successful because I can see the DB and it’s tables in the connections tab.

Then I go to call a simple pipe using dbplyr:

tbl(con, 'mtcars') %>%
  summarise(Count = n())

This is the point at which I get the error message:

Error: nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘mtcars’.

I have tried other variations on the theme such as tbl(con, 'dbo.mtcars')... or tbl(con, 'datawarehouse.dbo.mtcars'...) but no luck.

Answer

In the example you have linked to, mtcars is a table in datawarehouse. I am going to assume mtcars is in the database you are connecting to. But you can check for this using:

'mtcars' %in% DBI::dbListTables(con)

If you want to query a table in a specific database or schema (not the default) then you need to use in_schema.

Without in_schema:

tbl(con, 'dbo.mtcars')

Produces an sql query like:

SELECT *
FROM "dbo.mtcars"

Where the " delimit names. So in this case SQL is looking for a table named dbo.mtcars not a table named mtcars in dbo.

With in_schema:

tbl(con, in_schema('dbo','mtcars'))

Produces an sql query like:

SELECT *
FROM "dbo"."mtcars"

So in this case SQL is looking for a table named mtcars in dbo. Because each term is " quoted separately.