Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL Server requires SET QUOTED_IDENTIFIER ON #209

Closed
krlmlr opened this issue Aug 22, 2018 · 18 comments
Closed

SQL Server requires SET QUOTED_IDENTIFIER ON #209

krlmlr opened this issue Aug 22, 2018 · 18 comments
Labels
documentation mssql Microsoft SQL Server

Comments

@krlmlr
Copy link
Member

@krlmlr krlmlr commented Aug 22, 2018

for proper handling of quotes:

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 (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.

@krlmlr
Copy link
Member Author

@krlmlr krlmlr commented Aug 22, 2018

Can't replicate anymore. Do the ANSI_DEFAULTS survive the session? Will reopen with a reprex if necessary.

@krlmlr krlmlr closed this as completed Aug 22, 2018
@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

Just FYI, I'm running into this with both odbc and RJDBC...

Your solution here worked for me. Maybe put that in the docs? Make it an option somewhere?

@jimhester jimhester added the docs label Sep 5, 2018
@jimhester jimhester reopened this Sep 5, 2018
@jimhester
Copy link
Contributor

@jimhester jimhester commented Sep 5, 2018

@edgararuiz maybe we should add this suggestion to the known issues at https://db.rstudio.com/databases/microsoft-sql-server/?

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

I think you all knew this, but I didn't: changing the dplyr options above does not change the quoting, which is a different issue. To make dplyr work with my SQL Server I need the QUOTED_IDENTIFIER set to on.

I think adding it to the db.rstudio.com makes sense to me. Note this however:

#odbc do this
DBI::dbExecute(con, "SET QUOTED_IDENTIFIER ON")

## do RJDBC this way
RJDBC::dbSendUpdate(con, "SET QUOTED_IDENTIFIER ON")

It appears that RJDBC does not play well with DBI::dbExecute.

@edgararuiz-zz
Copy link

@edgararuiz-zz edgararuiz-zz commented Sep 5, 2018

@CerebralMastication , so you had to do this in order to get it to work?

## do RJDBC this way
RJDBC::dbSendUpdate(con, "SET QUOTED_IDENTIFIER ON")

If so, we can add that to the page.

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

Yes, when using RJDBC on SQL Server that worked for me.

When using odbc for connectivity to SQL Server I could use DBI::dbExecute(con, "SET QUOTED_IDENTIFIER ON")

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

however neither odbc nor RJDBC would work for me without flipping the QUOTED_IDENTIFIER bit.

FWIW, I am not admin on my SQL Server box so I have no idea if this is generally applicable to other SQL Server instances or some idiosyncrasy with our configuration.

@edgararuiz-zz
Copy link

@edgararuiz-zz edgararuiz-zz commented Sep 5, 2018

Oh ok, so this a work around for a very specific case. Are you using FreeTDS, as stated by @krlmlr at the top of this issue? If not, what driver are you using? and, are you using the same driver for both RJDBC and odbc connections?

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

I am using the Simba driver for odbc and the jtds driver for RJDBC

@edgararuiz-zz
Copy link

@edgararuiz-zz edgararuiz-zz commented Sep 5, 2018

Ok, we may also need to capture your package levels. Can you load odbc, RJDBC, dbplyr, DBI and post the sessionInfo() please?

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

Happy to:

> sessionInfo()
R version 3.5.1 (2018-07-02)
Platform: x86_64-apple-darwin15.6.0 (64-bit)
Running under: macOS High Sierra 10.13.6

Matrix products: default
BLAS: /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/3.5/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] odbc_1.1.6      rnr.r_0.1.0     forcats_0.3.0   stringr_1.3.1   dplyr_0.7.6    
 [6] purrr_0.2.5     readr_1.1.1     tidyr_0.8.1     tibble_1.4.2    ggplot2_3.0.0  
[11] tidyverse_1.2.1 getPass_0.2-2   RJDBC_0.2-7.1   rJava_0.9-10    DBI_1.0.0      

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.18     dbplyr_1.2.2     cellranger_1.1.0 plyr_1.8.4       pillar_1.3.0    
 [6] compiler_3.5.1   bindr_0.1.1      tools_3.5.1      bit_1.1-14       lubridate_1.7.4 
[11] jsonlite_1.5     nlme_3.1-137     gtable_0.2.0     lattice_0.20-35  pkgconfig_2.0.2 
[16] rlang_0.2.2      cli_1.0.0        rstudioapi_0.7   haven_1.1.2      bindrcpp_0.2.2  
[21] withr_2.1.2      xml2_1.2.0       httr_1.3.1       hms_0.4.2        bit64_0.9-7     
[26] grid_3.5.1       tidyselect_0.2.4 glue_1.3.0       R6_2.2.2         readxl_1.1.0    
[31] modelr_0.1.2     blob_1.1.1       magrittr_1.5     backports_1.1.2  scales_1.0.0    
[36] rvest_0.3.2      assertthat_0.2.0 colorspace_1.3-2 stringi_1.2.4    lazyeval_0.2.1  
[41] munsell_0.5.0    broom_0.5.0      crayon_1.3.4    

@jimhester
Copy link
Contributor

@jimhester jimhester commented Sep 5, 2018

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

So based on this I think you should really be setting this in the connection attributes / your DSN settings, not as a separate statement after connecting. Also the native ODBC driver on windows sets this by default, so this would only be something you need to configure when using freetds drivers; likely the Microsoft linux Driver and Simba's drivers already set this (which is likely why I never ran into this problem).

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

My use case is that I'm on Linux/Mac but I need Windows Authentication. The MSFT drivers do not support Windows Authentication on Linux/Mac. So I am forced to use Simba for odbc or jtds for RJDBC. I will research and see if either of those drivers support doing this at the connection.

@CerebralMastication
Copy link

@CerebralMastication CerebralMastication commented Sep 5, 2018

Looks like neither the Simba ODBC driver nor the jtds RDBC driver support turning on QUOTED_IDENTIFIER at the connection string or DSN. It's no big deal for me to add the correct R code to set that after I do my connection. But it really should be documented somewhere for the next fool like me to stumble upon.

@freddy77
Copy link

@freddy77 freddy77 commented May 15, 2019

FreeTDS ODBC driver turns on by default the QUOTED_IDENTIFIER setting connecting to MS server.
Exceptions are DB-library (which is not ODBC) and a TDS protocol < 7.0.
Can you use TDSDUMP to confirm which setting issue you are having?

@freddy77
Copy link

@freddy77 freddy77 commented May 15, 2019

My use case is that I'm on Linux/Mac but I need Windows Authentication. The MSFT drivers do not support Windows Authentication on Linux/Mac. So I am forced to use Simba for odbc or jtds for RJDBC. I will research and see if either of those drivers support doing this at the connection.

Also supported by FreeTDS driver.

@jimhester
Copy link
Contributor

@jimhester jimhester commented Nov 15, 2019

You should be able to set QUOTED_IDENTIFIER in your connection string with QuotedId=yes

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-ver15

@edgararuiz QuotedId=yes should probably be added to the list of recommended connection settings at https://db.rstudio.com/databases/microsoft-sql-server/#connection-settings

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
documentation mssql Microsoft SQL Server
Projects
None yet
Development

No branches or pull requests

5 participants