My question is: how to use dplyr features like tbl in SQL Server tables that don't use the standard dbo schema?
For more context, I'm trying to apply the R database example given here to my own tables: https://db.rstudio.com/ (scroll down to the Quick Example section).
It starts off fine. This first section works 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 can connect to my SQL Server and upload to tables in my database. I know this because
DBI::dbListTables(con)
returns the names of my available tables (but without any schema).
The following line of sample code also works when applied to one of my own tables, returning the column names in the table.
DBI::dbListFields(con, "mytable1")
However, as soon as I try to run the following line:
dplyr::tbl(con, "mytable1")
I get the error Invalid object name 'mytable1' , and not the expected table preview, as in the example.
This error does not occur when I run the same code in another mytable2 table. This time, as expected, I get a preview of mytable2 at startup:
dplyr::tbl(con, "mytable2")
One difference between mytable1 and mytable2 is the schema. mytable1 uses the created "abc" schema, that is, mydatabase.abc.mytable1. mytable2 uses the standard dbo scheme, that is, mydatabase.dbo.mytable2.
I tried dplyr::tbl(con, "abc.mytable1") but I get the same error Invalid object name . Similarly, when I tried dplyr::tbl(con, "dbo.mytable2") (although it works fine when I exclude the dbo part).
So, how can I use dplyr features like tbl in SQL Server tables that don't use the standard dbo schema? Thanks.