Pulling data from a linked SQL Server using DPLYR

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.

----------------------------------

Update

There are now two other approaches available:

1. in_catalog is designed to receive database and schema name. For example: in_catalog("database","schema","table").

2. The dbplyr documentation for in_schema and in_catalog now recommends I() instead. For example: I("database.schema.table"). With I() you should be able to use square-brackets if needed: I("[database].[schema].[table]").

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