copy_to fails to overwrite a temporary table

[mkirzon]

Using SQL Server connections, overwriting a table created by copy_to() with temporary=TRUE does not work. As a workaround, we first have to run try(db_drop_table(con, "##test")) between calls to copy_to to ensure we can overwrite the temp table.

library(tidyverse)

# Initialize 2 dataframes
df1 = data_frame(value = c(1,2,3))
df2 = data_frame(value = c(4,5,6))

# Initialize connection and upload the first dataframe
con = DBI::dbConnect(odbc::odbc(), Driver = "SQL Server", Server = "SQLServerName", Database = "DBName")
tbl_tmp = copy_to(con, df1, name = "test", temporary = TRUE)
#> Created a temporary table named: ##test

# Test the first upload
tbl_tmp
#> # Source:   table<##test> [?? x 1]
#> # Database: Microsoft SQL Server
#> #   13.00.5216[user@SQLServerName/DBName]
#>   value
#>   <dbl>
#> 1     1
#> 2     2
#> 3     3

# Overwrite original table with the new dataframe
tbl_tmp = copy_to(con, df2, name = "test", temporary = TRUE, overwrite = TRUE)
#> Created a temporary table named: ##test
#> Error: <SQL> 'CREATE TABLE "##test" (
#>   "value" FLOAT
#> )
#> '
#>   nanodbc/nanodbc.cpp:1587: 42S01: [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named '##test' in the database.

[hadley]

I don't have SQL server locally — while I've simulated the change and I'm reasonably certain it should fix the problem, I would appreciate someone (@edgararuiz ?) also checking this with a real SQL server instance.

[krlmlr]

FWIW, ## denotes global temporary tables, and # denotes local ones (https://stackoverflow.com/a/2921091/946850). Global temporary tables aren't auto-destructed when the connection terminates.

A pull request follows. I'm working on a terraformed setup of various databases on Azure for testing, happy to share.

[mkirzon]

I saw this was closed in the March commit and mentioned in the change log. However, the same exact reprex fails in dbplyr 1.4.0 on sql server

[florisvdh]

Having met this same problem (with dbplyr at current master), I see that both db_has_table() and dbExistsTable() return FALSE, for a temporary table which is present in the SQL Server database. From the traceback I see that db_has_table() is evaluated before writing (even while overwrite = TRUE) and this erroneous result possibly causes the problem.

I currently use the suggested workaround from above: try(db_drop_table(con, "##test"), silent = TRUE), which works.

An extract from my traceback:

11. `db_write_table.Microsoft SQL Server`(con, table, types = types,      values = values, temporary = temporary) at dbplyr.R#90

10. db_write_table(con, table, types = types, values = values, temporary = temporary) at verb-copy-to.R#116

9. with_transaction(con, {
    if (overwrite && !is_false(db_has_table(con, table))) {
        db_drop_table(con, table, force = TRUE)
    } ... at verb-copy-to.R#110

[florisvdh]

This is to add that currently (using dbplyr 2.1.1), try(db_drop_table(con, "##test"), silent = TRUE) as a workaround does not work anymore - see also #563. And copy_to(overwrite = TRUE) still errors - so I think the current issue should be reopened:

> try(copy_to(con, test, "##test", overwrite = TRUE))
Error in new_result(connection@ptr, statement, immediate) : 
  nanodbc/nanodbc.cpp:1594: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named '##test' in the database.

Both db_drop_table() and db_has_table() currently return an error (#563):

> try(db_drop_table(con, "##test"))
Error in UseMethod("db_drop_table") : 
  no applicable method for 'db_drop_table' applied to an object of class "c('Microsoft SQL Server', 'OdbcConnection', 'DBIConnection', 'DBIObject')"
> db_has_table(con, "##test")
Error in UseMethod("db_has_table") : 
  no applicable method for 'db_has_table' applied to an object of class "c('Microsoft SQL Server', 'OdbcConnection', 'DBIConnection', 'DBIObject')"

However the following is a functional workaround - instead of db_drop_table() you can use:

DBI::dbRemoveTable(con, "##test")