How to use dplyr tbl on a SQL Server non-standard schema table

Question

My question is how can I use dplyr functions, such as tbl, on SQL Server tables that do not use the default "dbo" schema?

For more context, I am trying to apply the R database example given here to my own tables: https://db.rstudio.com/ (scroll down to the section entitle "Quick Example").

It starts out ok. This first section runs fine:

install.packages("dplyr")
install.packages("odbc")
install.packages("dbplyr")
install.packages("DBI")

con <- DBI::dbConnect(odbc::odbc(),
                   Driver    = "SQL Server", 
                   Server    = [My Server Name],
                   Database  = "mydatabase",
                   UID       = [My User ID],
                   PWD       = [My Password],
                   Port      = 1433)

I am able to connect to my SQL Server and load in the tables in my database. I know this because

DBI::dbListTables(con)

returns the names of my available tables (but without any schema).

The next line of example code also works when applied to one of my own tables, returning the names of the columns in the table.

DBI::dbListFields(con, "mytable1")

However, once I try to run the next line:

dplyr::tbl(con, "mytable1")

I get an Invalid object name 'mytable1' error, rather than the expected table preview as in the example.

This error does not arise when I run the same code on a different table, mytable2. This time, as expected, I get a preview of mytable2 when I run:

dplyr::tbl(con, "mytable2")

One difference between mytable1 and mytable2 is the schema. mytable1 uses a made-up "abc" schema i.e. mydatabase.abc.mytable1. mytable2 uses the default "dbo" schema i.e. mydatabase.dbo.mytable2.

I tried dplyr::tbl(con, "abc.mytable1") but I get the same Invalid object name error. Likewise when I tried dplyr::tbl(con, "dbo.mytable2") (although it runs fine when I exclude the dbo part).

So how can I use dplyr functions, such as tbl, on SQL Server tables that do not use the default "dbo" schema? Thanks.

Answer 1

You can use dbplyr::in_schema.

In your case:

dplyr::tbl(con, dbplyr::in_schema("abc", "mytable1"))