I'm on Linux. R v3.4.3, rstudio v 1.1.423, unixodbc installed, R odbc package installed.
I have an ODBC Data source configured properly, I can access MS SQL Server with my R code inside a chunk within an Rmd file.
However when I try to connect using the rstudio GUI tools it does not work.
What I do is:
Connections pane - open connections - open schemas/catalogs (expand blue icon from > to v) - click on table icon.
When I try to access a table by clicking on the table icon, I get a dialog box similar to a javascipt alertbox, saying "R code execution error", and in the rstudio console tab, there appears this:
Error: <SQL> 'SELECT * FROM "bcdp"."dbo"."BCDP_BOXES"'
nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]Incorrect syntax near 'bcdp'.
This SQL code is principally correct, it's the double-quotes that the MS SQL Server does not understand.
Can I configure Rstudio somewhere to NOT inject these quotes?
RStudio itself does not produce that SQL query; it is produced by the odbc package (which acts as a kind of plug-in to provide ODBC data to the Connections pane and associated data views). You might ask over there, or file an issue if it appears to be a bug.
I am receiving a similar error, and though it is thrown by odbc, it may be caused by the interaction between the RStudio connections pane and odbc. On macOS 10.13.6 I connect to MariaDB 10.3.9 and a list of tables is displayed in the connections pane. The first time I click on the triangle to open a table, the following error is thrown:
Error in connection_sql_columns(connection@ptr, table_name = validateObjectName(table, :
nanodbc/nanodbc.cpp:2525: 22003: [ma-3.0.2][10.3.9-MariaDB]Numeric value out of range
Closing the table display and reopening (i.e. two more clicks on the triangle) works fine. The columns and their types are properly displayed, preview works, as do SQL queries. I'll post to r-dbi/odbc but wanted to inform the RStudio community of the issue.