`dbSendQuery()` not working with MS SQL Server 2022 new build version

[mplex]

Recently, the Microsoft SQL Server I am using has been upgraded from version 16.0.1000.6 to version 2022 (16.00.4175), and the dbSendQuery() function started to produce the error message:

Error: nanodbc/nanodbc.cpp:1769: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '<TABLENAME>'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
<SQL> 'SELECT * FROM <TABLENAME>'

I tested the query in the same R session with other databases running older versions, and they worked without issues. I am using version 1.2.3 of the "DBI" package from CRAN.

I'm wondering if this might be a bug that requires a patch.

[detule]

Hi there @mplex:

As best as I can tell both 16.0.1000.6 and 16.00.4175 are both SQL Server 2022. Has anything else changed in your environment? Which version of our package are you using? Which driver and driver version? What operating system, version of [R] etc?

Can you send us the output of conn@info for connections where the query is working and connections where the query is not working?

[mplex]

Hi @detule:

Many thanks for the prompt response.

As far as I am concerned, nothing changed in the environment, except the updated version of the MS SQL Server Management Studio (19.3.4.0), which was from SQL server 2022 Build 16.0.1000.6 to SQL server 2022 Build 16.0.4175.1.

Before this updating DBI::dbSendQuery() did not produce any error message, and returned (something like):

<OdbcResult>
  SQL  SELECT * FROM <TABLE-NAME>
  ROWS Fetched: 0 [incomplete]
       Changed: 0

The information about R session is:

sessionInfo()
#R version 4.3.1 (2023-06-16 ucrt)
#Platform: x86_64-w64-mingw32/x64 (64-bit)
#Running under: Windows Server 2022 x64 (build 20348)
#
#Matrix products: default
#
#
#locale:
#[1] LC_COLLATE=Danish_Denmark.utf8  LC_CTYPE=Danish_Denmark.utf8    LC_MONETARY=Danish_Denmark.utf8 LC_NUMERIC=C                    LC_TIME=Danish_Denmark.utf8    
#
#time zone: Europe/Copenhagen
#tzcode source: internal
#
#attached base packages:
#[1] stats     graphics  grDevices utils     datasets  methods   base     
#
#loaded via a namespace (and not attached):
# [1] bit_4.5.0.1     odbc_1.3.5      compiler_4.3.1  cli_3.6.4       hms_1.1.3       DBI_1.2.3       Rcpp_1.0.11     bit64_4.6.0-1   vctrs_0.6.5     blob_1.2.4      RODBC_1.3-26    xlsxjars_0.6.1  lifecycle_1.0.4 pkgconfig_2.0.3
#[15] rlang_1.1.5     rJava_1.0-11    xlsx_0.6.5

And for the connection (without the sensitive information):

conn
#<OdbcConnection> <DOM>\<USER>@<DATA-SOURCE-NAME>
#  Database: <DB-NAME>
#  Microsoft SQL Server Version: 16.00.4175

conn@info
#$dbname
#[1] "<DB-NAME>"
#
#$dbms.name
#[1] "Microsoft SQL Server"
#
#$db.version
#[1] "16.00.4175"
#
#$username
#[1] "<DOM>\\<USER>"
#
#$host
#[1] ""
#
#$port
#[1] ""
#
#$sourcename
#[1] "<DATA-SOURCE-NAME>"
#
#$servername
#[1] "<DSN>"
#
#$drivername
#[1] "msodbcsql17.dll"
#
#$odbc.version
#[1] "03.80.0000"
#
#$driver.version
#[1] "17.10.0006"
#
#$odbcdriver.version
#[1] "03.80"
#
#$supports.transactions
#[1] TRUE
#
#$getdata.extensions.any_column
#[1] FALSE
#
#$getdata.extensions.any_order
#[1] FALSE
#
#attr(,"class")
#[1] "Microsoft SQL Server" "driver_info"          "list" 

Best.

[detule]

Thanks!

• At the risk of moving too many levers at the same time - do you have the ability to update package:odbc to something more recent. I think the version you are using is more than two years old. But it's reassuring to know that it's not something that we've recently done in the package that is contributing to your problems.
• Can you also share how you are establishing the connection / with what parameters. Please do scrub any sensitive information.
• Does your table name have any non-ansi characters? Do you get the same behavior regardless of which table you query?

[detule]

Had a chance to update the server I test against to get a sense if there is something generically wrong with how we interact with that version, and nothing jumped out, at least at first glance.

  > head(dbGetQuery(conn, "SELECT AddressID, AddressLine1, City FROM AdventureWorks2017.Person.Address"))
    AddressID                   AddressLine1                 City
  1       532        #500-75 O'Connor Street               Ottawa
  2       497      #9900 2700 Production Way              Burnaby
  3     29781           00, rue Saint-Lazare            Dunkerque
  4     24231          02, place de Fontenoy Verrieres Le Buisson
  5     19637 035, boulevard du Montparnasse Verrieres Le Buisson
  6     15671 081, boulevard du Montparnasse          Saint-Denis
> conn@info
  $dbname
  [1] "master"

  $dbms.name
  [1] "Microsoft SQL Server"

  $db.version
  [1] "16.00.4175"
...
  $drivername
  [1] "libmsodbcsql-17.10.so.6.1"

  $odbc.version
  [1] "03.52"

  $driver.version
  [1] "17.10.0006"
...

[mplex]

Many thanks @detule for your replies.

The statement of the SQL queries I used had only the <TABLE> name, and I assume that before the database and schema names were taken from the DSN connection information.

It seems that the last Build 16.0.4175.1 of the SQL server 2022 requires explicitly the database and schema names in the query as the example you provided in your last post, and now this statement works.

DBI::dbSendQuery(conn, statement=paste("SELECT", "*", "FROM", "<DATABASE-NAME>.<SCHEMA-NAME>.<TABLE-NAME>", sep=" "))
#<OdbcResult>
#  SQL  SELECT * FROM <DB>.<SCHEMA>.<TABLE>
#  ROWS Fetched: 0 [incomplete]
#       Changed: 0

And this applies as well to function DBI::dbGetQuery().

Just in case, I updated the odbc package before testing.

All the best.