Error creating temporary tables with Microsoft SQL Server connection

[lymanmark]

I am getting an error when trying to create temporary tables using compute and copy_to with Microsoft SQL Server connection. As shown below, I can create non-temporary tables.

library(tidyverse)

my_con <-
  DBI::dbConnect(
    odbc::odbc(),
    Driver = "SQL Server",
    Server = ".\\SQLExpress",
    Database = "temp",
    Trusted_Connection = "True"
  )

iris %>% 
  copy_to(my_con, ., "iris")
#> Created a temporary table named: #iris
#> Error in result_insert_dataframe(rs@ptr, values, batch_rows): nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#iris'.  [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

my_table <- iris %>% 
  copy_to(my_con, ., "iris", temporary = FALSE)

my_table %>% 
  compute()
#> Created a temporary table named: #dbplyr_001
#> Error: nanodbc/nanodbc.cpp:1617: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Table '#dbplyr_001' does not exist. 
#> <SQL> 'UPDATE STATISTICS "#dbplyr_001"'

^{Created on 2020-04-29 by the reprex package (v0.3.0)}

[saishton]

I've also been having this issue (with left_joins) - it's to do with the change from using ##iris as the name of temporary tables to #iris.

[saishton]

@lymanmark I'm not sure if there are any impacts to this, but running the following as part of my code fixes this for me:

mssql_temp_name_working <- function(name, temporary) {
  if (temporary && substr(name, 1, 1) != "#") {
    name <- paste0("##", name)
    message("Created a temporary table named: ", name)
  }
  name
}

R.utils::reassignInPackage("mssql_temp_name", "dbplyr", mssql_temp_name_working)

rm(mssql_temp_name_working)

[lymanmark]

@saishton Thanks for the suggestion. As you suggest, I'm always a little leery of modifying code in a package. However, I was able to get it to work with the following work-around:

my_table %>%
     compute("##iris")

[krlmlr]

Thanks. Can you please try #435? This should enable local temporary tables:

remotes::install_github("tidyverse/dbplyr#435")

[saishton]

Hi @krlmlr,

I tried #435 (so am now running the dbplyr_1.4.3.9000 namespace), but the problem persists.

[florisvdh]

@krlmlr Having run into the same problem today, I can also confirm that #435 currently doesn't fix the issue.

Explicitly using "##tablename" does work:

> mydf <- tibble(a = 1:5, b = letters[1:5])
> con
<OdbcConnection> xxxxxxxxxxxx
  Database: xxxxxxxxxxxxxxxx
  Microsoft SQL Server Version: 13.00.5598
> copy_to(con, mydf)
Created a temporary table named: #mydf
 Error in result_insert_dataframe(rs@ptr, values, batch_rows) : 
  nanodbc/nanodbc.cpp:1617: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#mydf'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
> tbl(con, "##mydf")
 Error: nanodbc/nanodbc.cpp:1617: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '##mydf'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
<SQL> 'SELECT *
FROM "##mydf" AS "zzz1"
WHERE (0 = 1)' > 

> copy_to(con, mydf, name = "##mydf")
> tbl(con, "##mydf")
# Source:   table<##mydf> [?? x 2]
# Database: Microsoft SQL Server
      a b    
  <int> <chr>
1     1 a    
2     2 b    
3     3 c    
4     4 d    
5     5 e  

[krlmlr]

Thanks for the heads-up. What error are you seeing with #435?

[florisvdh]

The above error, i.e. the same as posted in this issue:

> copy_to(con, mydf)
Created a temporary table named: #mydf
 Error in result_insert_dataframe(rs@ptr, values, batch_rows) : 
  nanodbc/nanodbc.cpp:1617: 00000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#mydf'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 

[krlmlr]

Just to double-check: have you restarted your R session? What version of the odbc package are you using?

[florisvdh]

I could reproduce it after restarting R. Using odbc 1.2.2.

sessioninfo::session_info()

─ Session info ───────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.6.3 (2020-02-29)
 os       Linux Mint 18.1             
 system   x86_64, linux-gnu           
 ui       RStudio                     
 language (EN)                        
 collate  nl_BE.UTF-8                 
 ctype    nl_BE.UTF-8                 
 tz       Europe/Brussels             
 date     2020-05-11                  

─ Packages ───────────────────────────────────────────────────────────────────────────────────
 ! package     * version    date       lib source                          
   assertable    0.2.7      2019-09-21 [1] CRAN (R 3.6.1)                  
   assertthat    0.2.1      2019-03-21 [1] CRAN (R 3.6.0)                  
   backports     1.1.6      2020-04-05 [1] CRAN (R 3.6.3)                  
   bit           1.1-15.2   2020-02-10 [1] CRAN (R 3.6.2)                  
   bit64         0.9-7      2017-05-08 [1] CRAN (R 3.6.0)                  
   blob          1.2.1      2020-01-20 [1] CRAN (R 3.6.2)                  
   callr         3.4.3      2020-03-28 [1] CRAN (R 3.6.3)                  
   class         7.3-17     2020-04-26 [4] CRAN (R 3.6.3)                  
   classInt      0.4-3      2020-04-07 [1] CRAN (R 3.6.3)                  
   cli           2.0.2      2020-02-28 [1] CRAN (R 3.6.3)                  
   colorspace    1.4-1      2019-03-18 [1] CRAN (R 3.6.0)                  
   crayon        1.3.4      2017-09-16 [1] CRAN (R 3.6.0)                  
   crosstalk     1.1.0.1    2020-03-13 [1] CRAN (R 3.6.3)                  
   curl          4.3        2019-12-02 [1] CRAN (R 3.6.2)                  
   data.table    1.12.8     2019-12-09 [1] CRAN (R 3.6.2)                  
   DBI           1.1.0      2019-12-15 [1] CRAN (R 3.6.2)                  
   dbplyr        1.4.3.9000 2020-05-11 [1] Github (krlmlr/dbplyr@0c02787)  
   desc          1.2.0      2018-05-01 [1] CRAN (R 3.6.0)                  
   devtools      2.3.0      2020-04-10 [1] CRAN (R 3.6.3)                  
   dgof          1.2        2013-10-25 [1] CRAN (R 3.6.0)                  
   digest        0.6.25     2020-02-23 [1] CRAN (R 3.6.3)                  
   dplyr       * 0.8.5      2020-03-07 [1] CRAN (R 3.6.3)                  
   drat          0.1.5      2019-03-28 [1] CRAN (R 3.6.0)                  
   e1071         1.7-3      2019-11-26 [1] CRAN (R 3.6.2)                  
   ellipsis      0.3.0      2019-09-20 [1] CRAN (R 3.6.1)                  
   fansi         0.4.1      2020-01-08 [1] CRAN (R 3.6.2)                  
   fs            1.4.1      2020-04-04 [1] CRAN (R 3.6.3)                  
   generics      0.0.2      2018-11-29 [1] CRAN (R 3.6.0)                  
   geoaxe        0.1.0      2016-02-19 [1] CRAN (R 3.6.0)                  
   ggplot2       3.3.0      2020-03-05 [1] CRAN (R 3.6.3)                  
   glue          1.4.0      2020-04-03 [1] CRAN (R 3.6.3)                  
   gtable        0.3.0      2019-03-25 [1] CRAN (R 3.6.0)                  
   hms           0.5.3      2020-01-08 [1] CRAN (R 3.6.2)                  
   htmltools     0.4.0      2019-10-04 [1] CRAN (R 3.6.1)                  
   htmlwidgets   1.5.1      2019-10-08 [1] CRAN (R 3.6.1)                  
   httr          1.4.1      2019-08-05 [1] CRAN (R 3.6.1)                  
   inborutils    0.1.0.9081 2020-05-06 [1] Github (inbo/inborutils@6341166)
   iterators     1.0.12     2019-07-26 [1] CRAN (R 3.6.1)                  
   jsonlite      1.6.1      2020-02-02 [1] CRAN (R 3.6.2)                  
   KernSmooth    2.23-17    2020-04-26 [4] CRAN (R 3.6.3)                  
   KSgeneral     0.1.2      2020-02-11 [1] CRAN (R 3.6.3)                  
   lattice       0.20-41    2020-04-02 [4] CRAN (R 3.6.3)                  
   lazyeval      0.2.2      2019-03-15 [1] CRAN (R 3.6.3)                  
   leaflet       2.0.3      2019-11-16 [1] CRAN (R 3.6.2)                  
   lifecycle     0.2.0      2020-03-06 [1] CRAN (R 3.6.3)                  
   lubridate     1.7.8      2020-04-06 [1] CRAN (R 3.6.3)                  
   magrittr      1.5        2014-11-22 [1] CRAN (R 3.6.0)                  
   MASS          7.3-51.6   2020-04-26 [4] CRAN (R 3.6.3)                  
   memoise       1.1.0      2017-04-21 [1] CRAN (R 3.6.0)                  
   munsell       0.5.0      2018-06-12 [1] CRAN (R 3.6.0)                  
   oai           0.3.0      2019-09-07 [1] CRAN (R 3.6.1)                  
   odbc          1.2.2      2020-01-10 [1] CRAN (R 3.6.2)                  
   packrat       0.5.0      2018-11-14 [1] CRAN (R 3.6.0)                  
   pillar        1.4.4      2020-05-05 [1] CRAN (R 3.6.3)                  
   pkgbuild      1.0.8      2020-05-07 [1] CRAN (R 3.6.3)                  
   pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 3.6.1)                  
   pkgload       1.0.2      2018-10-29 [1] CRAN (R 3.6.0)                  
   plyr          1.8.6      2020-03-03 [1] CRAN (R 3.6.3)                  
   prettyunits   1.1.1      2020-01-24 [1] CRAN (R 3.6.3)                  
   processx      3.4.2      2020-02-09 [1] CRAN (R 3.6.3)                  
   ps            1.3.3      2020-05-08 [1] CRAN (R 3.6.3)                  
   purrr         0.3.4      2020-04-17 [1] CRAN (R 3.6.3)                  
   R6            2.4.1      2019-11-12 [1] CRAN (R 3.6.2)                  
   Rcpp          1.0.4.6    2020-04-09 [1] CRAN (R 3.6.3)                  
   readr         1.3.1      2018-12-21 [1] CRAN (R 3.6.2)                  
   remotes       2.1.1      2020-02-15 [1] CRAN (R 3.6.3)                  
   rgbif         2.2.0      2020-03-09 [1] CRAN (R 3.6.3)                  
   rgeos         0.5-2      2019-10-03 [1] CRAN (R 3.6.1)                  
   rlang         0.4.6      2020-05-02 [1] CRAN (R 3.6.3)                  
   rprojroot     1.3-2      2018-01-03 [1] CRAN (R 3.6.0)                  
   RSQLite       2.2.0      2020-01-07 [1] CRAN (R 3.6.2)                  
   rstudioapi    0.11       2020-02-07 [1] CRAN (R 3.6.3)                  
   scales        1.1.0      2019-11-18 [1] CRAN (R 3.6.2)                  
   sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 3.6.0)                  
   sf            0.9-3      2020-05-04 [1] CRAN (R 3.6.3)                  
   sp            1.4-1      2020-02-28 [1] CRAN (R 3.6.3)                  
   stringi       1.4.6      2020-02-17 [1] CRAN (R 3.6.3)                  
   stringr       1.4.0      2019-02-10 [1] CRAN (R 3.6.0)                  
   testthat      2.3.2      2020-03-02 [1] CRAN (R 3.6.3)                  
   tibble        3.0.1      2020-04-20 [1] CRAN (R 3.6.3)                  
   tidyr         1.0.3      2020-05-07 [1] CRAN (R 3.6.3)                  
   tidyselect    1.0.0      2020-01-27 [1] CRAN (R 3.6.2)                  
   units         0.6-6      2020-03-16 [1] CRAN (R 3.6.3)                  
   usethis       1.6.1      2020-04-29 [1] CRAN (R 3.6.3)                  
   vctrs         0.2.4      2020-03-10 [1] CRAN (R 3.6.3)                  
 P watina      * 0.2.6.9000 2020-05-06 [?] local                           
   whisker       0.4        2019-08-28 [1] CRAN (R 3.6.1)                  
   withr         2.2.0      2020-04-20 [1] CRAN (R 3.6.3)                  
   xml2          1.3.2      2020-04-23 [1] CRAN (R 3.6.3)                  

[1] /home/floris/lib/R/library
[2] /usr/local/lib/R/site-library
[3] /usr/lib/R/site-library
[4] /usr/lib/R/library

[krlmlr]

Thanks. I updated the PR, could you please reinstall and try again?

[florisvdh]

Did that. Unfortunately, still the same error.

sessioninfo::session_info()

> sessioninfo::session_info()
─ Session info ───────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.6.3 (2020-02-29)
 os       Linux Mint 18.1             
 system   x86_64, linux-gnu           
 ui       RStudio                     
 language (EN)                        
 collate  nl_BE.UTF-8                 
 ctype    nl_BE.UTF-8                 
 tz       Europe/Brussels             
 date     2020-05-11                  

─ Packages ───────────────────────────────────────────────────────────────────────────────────
 ! package     * version    date       lib source                          
   assertable    0.2.7      2019-09-21 [1] CRAN (R 3.6.1)                  
   assertthat    0.2.1      2019-03-21 [1] CRAN (R 3.6.0)                  
   backports     1.1.6      2020-04-05 [1] CRAN (R 3.6.3)                  
   bit           1.1-15.2   2020-02-10 [1] CRAN (R 3.6.2)                  
   bit64         0.9-7      2017-05-08 [1] CRAN (R 3.6.0)                  
   blob          1.2.1      2020-01-20 [1] CRAN (R 3.6.2)                  
   callr         3.4.3      2020-03-28 [1] CRAN (R 3.6.3)                  
   class         7.3-17     2020-04-26 [4] CRAN (R 3.6.3)                  
   classInt      0.4-3      2020-04-07 [1] CRAN (R 3.6.3)                  
   cli           2.0.2      2020-02-28 [1] CRAN (R 3.6.3)                  
   colorspace    1.4-1      2019-03-18 [1] CRAN (R 3.6.0)                  
   crayon        1.3.4      2017-09-16 [1] CRAN (R 3.6.0)                  
   crosstalk     1.1.0.1    2020-03-13 [1] CRAN (R 3.6.3)                  
   curl          4.3        2019-12-02 [1] CRAN (R 3.6.2)                  
   data.table    1.12.8     2019-12-09 [1] CRAN (R 3.6.2)                  
   DBI           1.1.0      2019-12-15 [1] CRAN (R 3.6.2)                  
   dbplyr        1.4.3.9000 2020-05-11 [1] Github (krlmlr/dbplyr@47067fc)  
   desc          1.2.0      2018-05-01 [1] CRAN (R 3.6.0)                  
   devtools      2.3.0      2020-04-10 [1] CRAN (R 3.6.3)                  
   dgof          1.2        2013-10-25 [1] CRAN (R 3.6.0)                  
   digest        0.6.25     2020-02-23 [1] CRAN (R 3.6.3)                  
   dplyr       * 0.8.5      2020-03-07 [1] CRAN (R 3.6.3)                  
   drat          0.1.5      2019-03-28 [1] CRAN (R 3.6.0)                  
   e1071         1.7-3      2019-11-26 [1] CRAN (R 3.6.2)                  
   ellipsis      0.3.0      2019-09-20 [1] CRAN (R 3.6.1)                  
   fansi         0.4.1      2020-01-08 [1] CRAN (R 3.6.2)                  
   fs            1.4.1      2020-04-04 [1] CRAN (R 3.6.3)                  
   generics      0.0.2      2018-11-29 [1] CRAN (R 3.6.0)                  
   geoaxe        0.1.0      2016-02-19 [1] CRAN (R 3.6.0)                  
   ggplot2       3.3.0      2020-03-05 [1] CRAN (R 3.6.3)                  
   glue          1.4.0      2020-04-03 [1] CRAN (R 3.6.3)                  
   gtable        0.3.0      2019-03-25 [1] CRAN (R 3.6.0)                  
   hms           0.5.3      2020-01-08 [1] CRAN (R 3.6.2)                  
   htmltools     0.4.0      2019-10-04 [1] CRAN (R 3.6.1)                  
   htmlwidgets   1.5.1      2019-10-08 [1] CRAN (R 3.6.1)                  
   httr          1.4.1      2019-08-05 [1] CRAN (R 3.6.1)                  
   inborutils    0.1.0.9081 2020-05-06 [1] Github (inbo/inborutils@6341166)
   iterators     1.0.12     2019-07-26 [1] CRAN (R 3.6.1)                  
   jsonlite      1.6.1      2020-02-02 [1] CRAN (R 3.6.2)                  
   KernSmooth    2.23-17    2020-04-26 [4] CRAN (R 3.6.3)                  
   KSgeneral     0.1.2      2020-02-11 [1] CRAN (R 3.6.3)                  
   lattice       0.20-41    2020-04-02 [4] CRAN (R 3.6.3)                  
   lazyeval      0.2.2      2019-03-15 [1] CRAN (R 3.6.3)                  
   leaflet       2.0.3      2019-11-16 [1] CRAN (R 3.6.2)                  
   lifecycle     0.2.0      2020-03-06 [1] CRAN (R 3.6.3)                  
   lubridate     1.7.8      2020-04-06 [1] CRAN (R 3.6.3)                  
   magrittr      1.5        2014-11-22 [1] CRAN (R 3.6.0)                  
   MASS          7.3-51.6   2020-04-26 [4] CRAN (R 3.6.3)                  
   memoise       1.1.0      2017-04-21 [1] CRAN (R 3.6.0)                  
   munsell       0.5.0      2018-06-12 [1] CRAN (R 3.6.0)                  
   oai           0.3.0      2019-09-07 [1] CRAN (R 3.6.1)                  
   odbc          1.2.2      2020-01-10 [1] CRAN (R 3.6.2)                  
   packrat       0.5.0      2018-11-14 [1] CRAN (R 3.6.0)                  
   pillar        1.4.4      2020-05-05 [1] CRAN (R 3.6.3)                  
   pkgbuild      1.0.8      2020-05-07 [1] CRAN (R 3.6.3)                  
   pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 3.6.1)                  
   pkgload       1.0.2      2018-10-29 [1] CRAN (R 3.6.0)                  
   plyr          1.8.6      2020-03-03 [1] CRAN (R 3.6.3)                  
   prettyunits   1.1.1      2020-01-24 [1] CRAN (R 3.6.3)                  
   processx      3.4.2      2020-02-09 [1] CRAN (R 3.6.3)                  
   ps            1.3.3      2020-05-08 [1] CRAN (R 3.6.3)                  
   purrr         0.3.4      2020-04-17 [1] CRAN (R 3.6.3)                  
   R6            2.4.1      2019-11-12 [1] CRAN (R 3.6.2)                  
   Rcpp          1.0.4.6    2020-04-09 [1] CRAN (R 3.6.3)                  
   readr         1.3.1      2018-12-21 [1] CRAN (R 3.6.2)                  
   remotes       2.1.1      2020-02-15 [1] CRAN (R 3.6.3)                  
   rgbif         2.2.0      2020-03-09 [1] CRAN (R 3.6.3)                  
   rgeos         0.5-2      2019-10-03 [1] CRAN (R 3.6.1)                  
   rlang         0.4.6      2020-05-02 [1] CRAN (R 3.6.3)                  
   rprojroot     1.3-2      2018-01-03 [1] CRAN (R 3.6.0)                  
   RSQLite       2.2.0      2020-01-07 [1] CRAN (R 3.6.2)                  
   rstudioapi    0.11       2020-02-07 [1] CRAN (R 3.6.3)                  
   scales        1.1.0      2019-11-18 [1] CRAN (R 3.6.2)                  
   sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 3.6.0)                  
   sf            0.9-3      2020-05-04 [1] CRAN (R 3.6.3)                  
   sp            1.4-1      2020-02-28 [1] CRAN (R 3.6.3)                  
   stringi       1.4.6      2020-02-17 [1] CRAN (R 3.6.3)                  
   stringr       1.4.0      2019-02-10 [1] CRAN (R 3.6.0)                  
   testthat      2.3.2      2020-03-02 [1] CRAN (R 3.6.3)                  
   tibble        3.0.1      2020-04-20 [1] CRAN (R 3.6.3)                  
   tidyr         1.0.3      2020-05-07 [1] CRAN (R 3.6.3)                  
   tidyselect    1.0.0      2020-01-27 [1] CRAN (R 3.6.2)                  
   units         0.6-6      2020-03-16 [1] CRAN (R 3.6.3)                  
   usethis       1.6.1      2020-04-29 [1] CRAN (R 3.6.3)                  
   vctrs         0.2.4      2020-03-10 [1] CRAN (R 3.6.3)                  
 P watina      * 0.2.6.9000 2020-05-06 [?] local                           
   whisker       0.4        2019-08-28 [1] CRAN (R 3.6.1)                  
   withr         2.2.0      2020-04-20 [1] CRAN (R 3.6.3)                  
   xml2          1.3.2      2020-04-23 [1] CRAN (R 3.6.3)                  

[1] /home/floris/lib/R/library
[2] /usr/local/lib/R/site-library
[3] /usr/lib/R/site-library
[4] /usr/lib/R/library

[krlmlr]

Need to dig deeper. Can you please install https://github.com/r-dbi/dblog and use dblog::dblog(odbc::odbc()) instead of odbc::odbc()? This should give a fairly verbose log of the DBI operations that happen under the hood.

[florisvdh]

Thanks for the tip, will do.