```dplyr::copy_to``` fails with POSIXct data on updated SQL Server 2022

[LasseEngboChr]

On 2025-03-13 a new release of (MS) SQL Server 2022 ( Version: 16.00.4185) was released. The Github Actions for our SCDB package started failing a test. The command that fails is: dplyr::copy_to(conn, data_random) . I do not have access to this version of the SQL server locally so I have only been able to test using the Github Action - not efficient. The error I get is "[Microsoft][ODBC Driver 18 for SQL Server]Invalid SQL data type" - so it is somehow related to ODBC. By testing different datatypes in data_random I learned that it works fine except if the data include datetime with type POSIXct.

I have tried to peal of some layers and here is a small example:

conn <- <your connection to SQL Server 2022 Version: 16.00.4185 >
# Making a minimal test
    data_ok <- data.frame(
      "Date"      = Sys.Date(),
      "character" = "test",
      "integer"   = 1L,
      "numeric"   = 1,
      "logical"   = TRUE
    )

    data_datetime <- data_ok
    data_datetime$datetime <- Sys.time()

    print(conn)
    # Wanted to do
    # my_copy <- dplyr::copy_to(conn, data_datetime)

    DBI::dbWriteTable(conn, "#testthis", value = data_datetime, temporary = TRUE)
    print("testthis done")

    # dplyr::copy_to effectively calls SQL(as_table_path(<string>))
    # First without a datatime column
    DBI::dbWriteTable(conn, DBI::SQL(dbplyr::as_table_path("#testthis2A", conn)), value = data_ok, temporary = TRUE)
    print("testthis2A done")

    # Then with a datatime column
    DBI::dbWriteTable(conn, DBI::SQL("#testthis2B"), value = data_datetime, temporary = TRUE)
    print("testthis2B done")
    # We never get here

The only difference between testthis1 and 2B is that the name of the table is wrapped through SQL for 2B. That means that two different versions af dbWriteTable are used due the differences in signatures.
The backtrace information from testthis2B is:

[Microsoft][ODBC Driver 18 for SQL Server]Invalid SQL data type 
Backtrace:
    ▆
 1. ├─DBI::dbWriteTable(...) at test-getTableSignature.R:314:5
 2. └─DBI::dbWriteTable(...)
 3.   └─odbc (local) .local(conn, name, value, ...)
 4.     ├─DBI::dbAppendTable(...)
 5.     └─odbc::dbAppendTable(...)
 6.       └─odbc (local) .local(conn, name, value, ..., row.names = row.names)
 7.         ├─base::tryCatch(...)
 8.         │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
 9.         └─odbc:::result_insert_dataframe(rs@ptr, values, batch_rows)

I can see that dbWriteTable first calls dbCreateTable before calling dbAppendTable but this is where the complexity in following the S4 signatures and their subtle differences in methods with Github Actions as the only tool to debug made me write this issue - hoping that others can take take it the last steps.

I found the version of the SQL Server here (https://sqlserverbuilds.blogspot.com/2021/07/sql-server-2022-versions.html)

Versions of core packages and Sys.info:

[1] "ODBC"
[1] '1.6.1'
[1] "DBI"
[1] '1.2.3'
[1] "dplyr"
[1] '1.1.4'
# Sys.info()
                                     sysname 
                                     "Linux" 
                                     release 
                          "6.8.0-1021-azure" 
                                     version 
"#25-Ubuntu SMP Wed Jan 15 20:45:09 UTC 2025" 
                                    nodename 
                             "fv-az[136](https://github.com/ssi-dk/SCDB/actions/runs/14154135273/job/39651172456?pr=193#step:10:137)8-733" 
                                     machine 
                                    "x86_64" 
                                       login 
                                   "unknown" 
                                        user 
                                    "runner" 
                              effective_user 
                                    "runner"

Please let me know if further information is needed.

[detule]

Thanks for the report, and the minimal example! Appreciate the extra investigation to make it reproducible: I can replicate.

I'll try to make sense and get back to you in this thread.

[detule]

@LasseEngboChr Do you have the ability to test your pipeline with a development branch? I would hope that this might fix the issue you are seeing: https://github.com/detule/odbc/tree/fixup/columns_exact_propagation

Before submitting this as a PR to master here, I need to do a little bit more investigation to try and understand what made us sensitive to this issue. Thanks again.

[LasseEngboChr]

@detule Thank you for looking into this so swiftly!

At first I wanted to install locally, however I get an error when running:
remotes::install_github(repo = "detule/odbc", ref = "tree/fixup/columns_exact_propagation")
Cannot open: 'https://api.github.com/repos/detule/odbc/tarball/tree%2Ffixup%2Fcolumns_exact_propagation'

If you can instruct me how to, then I'll be happy to try!

[detule]

@detule Thank you for looking into this so swiftly!

At first I wanted to install locally, however I get an error when running: remotes::install_github(repo = "detule/odbc", ref = "tree/fixup/columns_exact_propagation") Cannot open: 'https://api.github.com/repos/detule/odbc/tarball/tree%2Ffixup%2Fcolumns_exact_propagation'

If you can instruct me how to, then I'll be happy to try!

Hey there:
I think perhaps try without the "tree" in the ref:
remotes::install_github(repo = "detule/odbc", ref = "fixup/columns_exact_propagation")

[detule]

Before submitting this as a PR to master here, I need to do a little bit more investigation to try and understand what made us sensitive to this issue. Thanks again.

Here's the postmortem:

BLUF: Combination of a buggy s4 dispatch + bug from upstream + complicated SQL Server temp table discovery.

The issue:

• When writing to temp tables "SQLDescribeParam" (call nanodbc uses to suss out the parameter types in the INSERT statement when writing) has failed probably forever.
  A. It fails for FreeTDS for all writes because this API end point is not implemented in the driver. It's a known failure point; we have workarounds for this.
  B. Apparently, it also fails for the OEM driver because the driver doesn't handle table names like '#testthis2A`, without the hashed suffix that makes up the real object name in the data base.

Focus here is on B.; we have various workarounds for A.

How did it work historically?

• We haven't been affected by this historically because we have a fallback that bypasses the failure at the cost of introducing an inefficient conversion from CHAR to the target data type. I always thought that the fallback is there to handle FreeTDS and other drivers that do not completely support the API ( and the inline comments supports this ), but apparently the fallback code-path is also reached with the OEM SQL Server driver when writing to temp tables. Probably means we've been writing inefficiently to temp tables for ever.

• More recently we also introduced another way to protect against this. We made the calls to odbcConnectionColumns, which is a secondary way we work around this issue, work for temp tables.

What happened recently to make it bite us?
Two things:

• There is a bug in the portion of this commit we brought down from upstream. It renders our fallback ineffective when writing to DATETIME2. That's my bad, I'll fix both here and upstream. [ Follow up 4/4: Upstream fix: https://github.com/bugfix: describe_data: if unsuccesful cleanup nanodbc/nanodbc#427 ]
• The secondary protection failed because the S4 dispatch in odbcConnectionTables_ incorrectly handled the combination of a SQL table name and the exact parameter being set to true. I'll fix this as well.

[LasseEngboChr]

@detule Thank you for looking into this so swiftly!
At first I wanted to install locally, however I get an error when running: remotes::install_github(repo = "detule/odbc", ref = "tree/fixup/columns_exact_propagation") Cannot open: 'https://api.github.com/repos/detule/odbc/tarball/tree%2Ffixup%2Fcolumns_exact_propagation'
If you can instruct me how to, then I'll be happy to try!

Hey there: I think perhaps try without the "tree" in the ref: remotes::install_github(repo = "detule/odbc", ref = "fixup/columns_exact_propagation")

Thank you once again!
I can confirm that the "fixup/columns_exact_propagation" branch solves the issue in my Github Actions :-)