jdlong
September 5, 2018, 11:26am
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!
opened 08:00AM - 22 Aug 18 UTC
closed 06:42PM - 15 Nov 19 UTC
mssql
documentation
for proper handling of quotes:
```r
con <- DBI::dbConnect(odbc::odbc(), ...)…
DBI::dbExecute(con, "SET QUOTED_IDENTIFIER ON")
```
Otherwise, `"` can't be used to delimit identifiers.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-2017.
This happens with a connection via FreeTDS, the [reference to `SET ANSI_DEFAULTS ON`](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-defaults-transact-sql?view=sql-server-2017) (a superset of `QUOTED_IDENTIFIER`) claims that this is set automatically for ODBC connections.
I wonder if we can/should be issuing this by default for connections to SQL Server, or if this is an upstream issue.
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