Changing the table quoting with SQL Translation

tidyverse
1

I'm using RJDBC to access a SQL Server database. Everything works pretty well except for one thing (that keeps it from working at all). dbplyr is defaulting to quoting table aliases in the resulting SQL like this:

SELECT
	top 5 *
FROM
	mydb.myschema.mytable "mt" 
	join mydb.myschema.myothertable "mot" on "mt".key = "mot".key

My SQL Server thinks that's a really bad idea and desperately wants me to put table aliases in square brackets:

SELECT
	top 5 *
FROM
	mydb.myschema.mytable [mt]
	join mydb.myschema.myothertable [mot] on [mt].key = [mot].key

I can see from the documentation that DBI:: dbQuoteIdentifier() is involved in this process, but I can't figure out how to tell it to use square brackets.

It feels like there should be a way to tell DBI that it should be using SQL Server norms here... but I can't figure out how. Or even just to override the table alias quoting.

Any ideas?

1 Like
2

it looks like the magic incantation requires telling SQL Server to allow quoted identifiers:

DBI::dbExecute(con, "SET QUOTED_IDENTIFIER ON")

Shout out to @krlmlr for having documented this in an odbc github issue! Thank you!

Also worth noting the RJDBC hints at the bottom of this page: Solutions - Microsoft SQL Server

in particular this:

  • RJDBC support - Even though it is not considered an issue, we have found a workaround. The approach is to point the current JDBC connection to the MS SQL translation inside dbplyr :
  sql_translate_env.JDBCConnection <- dbplyr:::`sql_translate_env.Microsoft SQL Server`
  sql_select.JDBCConnection <- dbplyr:::`sql_select.Microsoft SQL Server`

Please refer to the Issues section in dplyr to find out the latest regarding bugs and resolutions.

6 Likes