Pulling data from a linked SQL Server using DPLYR

Question

I am trying to use dplyr to pull data from a table on a linked SQL Server. I am able to connect and run queries using the dbGetQuery method as long as I (1) provide the fully qualified path ([server name].[database name].[schema name].[table name]) and (2) set up the dbConnect method without a DB parameter (so that the connection is to the server at large, not a specific DB). If I try to connect to the DB on the linked server I get a 'not found' error.

So I'm working with a server-level connection, but when I connect to the server and try to pull data from the SQL tables into a tbl object I get an 'invalid object name' error.

I am running

``tbl(con, dbplyr::in_schema("[server name].[database name].[schema name]", "table name")``

I have tried the same with in_catalog without success.

Is there a way to use these methods in dplyr to pull data from a linked server? I'd rather not resort running a select * if I can avoid it.

I am expecting to create a table in R studio based on the data in the linked server table. I am instead getting an error saying that I provided an 'invalid object name'. I have tried in_schema and in_catalog. I have confirmed the connection details as well.

Answer 1

This problem is caused by how in_schema operates. Similar to my answer here, we need to tell dbplyr that the server-database-schema string is already prepared sql.

---

Explanation

The command dbplyr::in_schema("text1", "text2") wants to treat all of text1 as the schema name, and all of text2 as the table name.

This causes a problem in a case like:

in_schema("[server name].[database name].[schema name]", "table name")

As [server name].[database name].[schema name] is not one large schema name but the combination of three separate names together. However, during translation, dbplyr wraps the entire argument in delimiters so that it is treated as if it were a single schema name.

---

Solution

Thankfully there is a way to prevent dbplyr from treating the entire argument as a single schema name: The sql() function tells dbplyr to treat its contents as if they were already converted to SQL.

So the solution is:

in_schema(sql("[server name].[database name].[schema name]"), "table name")

Some additional caution is required when using this method. Because dbplyr will not apply delimiters, it is best to ensure your inputs have their own delimiters.

Answer 2

You could also use DBI:Id(). For example, Id(schema = "schema name", table = "table name").