Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

MSSQL: Cannot append to temporary tables #584

Closed
jatherrien opened this issue Jun 30, 2023 · 2 comments · Fixed by #600
Closed

MSSQL: Cannot append to temporary tables #584

jatherrien opened this issue Jun 30, 2023 · 2 comments · Fixed by #600

Comments

@jatherrien
Copy link

Issue Description and Expected Result

Unable to append to temporary table due to dbExistsTable being blind to temporary tables. Given that the table exists and can be queried from, I expect to be able to append to it.

May be related to #509 ?

Database

Microsoft SQL Server 16.0.1000.6

Reproducible Example

library(odbc)
library(DBI)

con <- DBI::dbConnect(odbc::odbc(),
                                Driver = "ODBC Driver 18 for SQL Server",
                                SERVER = "<masked>",
                                Trusted_Connection="yes",
                                TrustServerCertificate="yes",
                                port = 1433)

dbExecute(con, "CREATE TABLE #my_temp (
               id int not null,
               x float not null,
               primary key (id)
               )", immediate=TRUE) # need immediate=TRUE for table to continue to exist
#> [1] 0

# We can verify that the table exists through select
dbGetQuery(con, "SELECT * FROM #my_temp")
#> [1] id x 
#> <0 rows> (or 0-length row.names)

dbAppendTable(con, "#my_temp", data.frame(id=1, x=5.0))
#> Error in dbAppendTable(con, "#my_temp", data.frame(id = 1, x = 5)): dbExistsTable(conn, name) is not TRUE
dbExistsTable(con, "#my_temp")
#> [1] FALSE
dbExistsTable(con, "#my_temp", immediate=TRUE)
#> [1] FALSE

It may be worth mentioning that I also experience this same issue on a Linux system as well.

Session Info 
devtools::session_info()
─ Session info ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.3.1 (2023-06-16 ucrt)
 os       Windows 11 x64 (build 22621)
 system   x86_64, mingw32
 ui       RStudio
 language (EN)
 collate  English_Canada.utf8
 ctype    English_Canada.utf8
 tz       America/Vancouver
 date     2023-06-30
 rstudio  2023.06.0+421 Mountain Hydrangea (desktop)
 pandoc   3.1.1 @ C:/Program Files/RStudio/resources/app/bin/quarto/bin/tools/ (via rmarkdown)

─ Packages ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 package     * version date (UTC) lib source
 bit           4.0.5   2022-11-15 [1] CRAN (R 4.3.1)
 bit64         4.0.5   2020-08-30 [1] CRAN (R 4.3.1)
 blob          1.2.4   2023-03-17 [1] CRAN (R 4.3.1)
 cachem        1.0.8   2023-05-01 [1] CRAN (R 4.3.1)
 callr         3.7.3   2022-11-02 [1] CRAN (R 4.3.1)
 cli           3.6.1   2023-03-23 [1] CRAN (R 4.3.1)
 clipr         0.8.0   2022-02-22 [1] CRAN (R 4.3.1)
 crayon        1.5.2   2022-09-29 [1] CRAN (R 4.3.1)
 DBI         * 1.1.3   2022-06-18 [1] CRAN (R 4.3.1)
 devtools      2.4.5   2022-10-11 [1] CRAN (R 4.3.1)
 digest        0.6.32  2023-06-26 [1] CRAN (R 4.3.1)
 ellipsis      0.3.2   2021-04-29 [1] CRAN (R 4.3.1)
 evaluate      0.21    2023-05-05 [1] CRAN (R 4.3.1)
 fansi         1.0.4   2023-01-22 [1] CRAN (R 4.3.1)
 fastmap       1.1.1   2023-02-24 [1] CRAN (R 4.3.1)
 fs            1.6.2   2023-04-25 [1] CRAN (R 4.3.1)
 glue          1.6.2   2022-02-24 [1] CRAN (R 4.3.1)
 hms           1.1.3   2023-03-21 [1] CRAN (R 4.3.1)
 htmltools     0.5.5   2023-03-23 [1] CRAN (R 4.3.1)
 htmlwidgets   1.6.2   2023-03-17 [1] CRAN (R 4.3.1)
 httpuv        1.6.11  2023-05-11 [1] CRAN (R 4.3.1)
 knitr         1.43    2023-05-25 [1] CRAN (R 4.3.1)
 later         1.3.1   2023-05-02 [1] CRAN (R 4.3.1)
 lifecycle     1.0.3   2022-10-07 [1] CRAN (R 4.3.1)
 magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.3.1)
 memoise       2.0.1   2021-11-26 [1] CRAN (R 4.3.1)
 mime          0.12    2021-09-28 [1] CRAN (R 4.3.0)
 miniUI        0.1.1.1 2018-05-18 [1] CRAN (R 4.3.1)
 odbc        * 1.3.5   2023-06-29 [1] CRAN (R 4.3.1)
 pillar        1.9.0   2023-03-22 [1] CRAN (R 4.3.1)
 pkgbuild      1.4.2   2023-06-26 [1] CRAN (R 4.3.1)
 pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.3.1)
 pkgload       1.3.2   2022-11-16 [1] CRAN (R 4.3.1)
 prettyunits   1.1.1   2020-01-24 [1] CRAN (R 4.3.1)
 processx      3.8.2   2023-06-30 [1] CRAN (R 4.3.1)
 profvis       0.3.8   2023-05-02 [1] CRAN (R 4.3.1)
 promises      1.2.0.1 2021-02-11 [1] CRAN (R 4.3.1)
 ps            1.7.5   2023-04-18 [1] CRAN (R 4.3.1)
 purrr         1.0.1   2023-01-10 [1] CRAN (R 4.3.1)
 R6            2.5.1   2021-08-19 [1] CRAN (R 4.3.1)
 Rcpp          1.0.10  2023-01-22 [1] CRAN (R 4.3.1)
 remotes       2.4.2   2021-11-30 [1] CRAN (R 4.3.1)
 reprex        2.0.2   2022-08-17 [1] CRAN (R 4.3.1)
 rlang         1.1.1   2023-04-28 [1] CRAN (R 4.3.1)
 rmarkdown     2.22    2023-06-01 [1] CRAN (R 4.3.1)
 rstudioapi    0.14    2022-08-22 [1] CRAN (R 4.3.1)
 sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.3.1)
 shiny         1.7.4   2022-12-15 [1] CRAN (R 4.3.1)
 stringi       1.7.12  2023-01-11 [1] CRAN (R 4.3.0)
 stringr       1.5.0   2022-12-02 [1] CRAN (R 4.3.1)
 tibble        3.2.1   2023-03-20 [1] CRAN (R 4.3.1)
 urlchecker    1.0.1   2021-11-30 [1] CRAN (R 4.3.1)
 usethis       2.2.1   2023-06-23 [1] CRAN (R 4.3.1)
 utf8          1.2.3   2023-01-31 [1] CRAN (R 4.3.1)
 vctrs         0.6.3   2023-06-14 [1] CRAN (R 4.3.1)
 withr         2.5.0   2022-03-03 [1] CRAN (R 4.3.1)
 xfun          0.39    2023-04-20 [1] CRAN (R 4.3.1)
 xtable        1.8-4   2019-04-21 [1] CRAN (R 4.3.1)
 yaml          2.3.7   2023-01-23 [1] CRAN (R 4.3.0)

 [1] C:/Users/jtherrien/AppData/Local/R/win-library/4.3
 [2] C:/Program Files/R/R-4.3.1/library
@ablack3
Copy link

ablack3 commented Aug 10, 2023

Also I cannot create temporary tables on MSSQL using DBI::dbWriteTable with temporary = TRUE. It looks like incorrect SQL is being used.

con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = Sys.getenv("SQL_SERVER_DRIVER"),
                      Server   = Sys.getenv("CDM5_SQL_SERVER_SERVER"),
                      Database = Sys.getenv("CDM5_SQL_SERVER_CDM_DATABASE"),
                      UID      = Sys.getenv("CDM5_SQL_SERVER_USER"),
                      PWD      = Sys.getenv("CDM5_SQL_SERVER_PASSWORD"),
                      TrustServerCertificate="yes",
                      Port     = 1433)


DBI::dbWriteTable(con, name = "temp_table", value = cars[1,], temporary = TRUE)
#> Error in eval(expr, envir, enclos): nanodbc/nanodbc.cpp:1708: 00000: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement.

DBI::dbDisconnect(con)

Created on 2023-08-10 with reprex v2.0.2

Session info 
sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.2.2 (2022-10-31)
#>  os       macOS Big Sur ... 10.16
#>  system   x86_64, darwin17.0
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       America/New_York
#>  date     2023-08-10
#>  pandoc   3.1.1 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  bit           4.0.5   2022-11-15 [1] CRAN (R 4.2.0)
#>  bit64         4.0.5   2020-08-30 [1] CRAN (R 4.2.0)
#>  blob          1.2.4   2023-03-17 [1] CRAN (R 4.2.0)
#>  cli           3.6.1   2023-03-23 [1] CRAN (R 4.2.0)
#>  DBI           1.1.3   2022-06-18 [1] CRAN (R 4.2.0)
#>  digest        0.6.33  2023-07-07 [1] CRAN (R 4.2.0)
#>  evaluate      0.21    2023-05-05 [1] CRAN (R 4.2.0)
#>  fastmap       1.1.1   2023-02-24 [1] CRAN (R 4.2.0)
#>  fs            1.6.3   2023-07-20 [1] CRAN (R 4.2.0)
#>  glue          1.6.2   2022-02-24 [1] CRAN (R 4.2.0)
#>  hms           1.1.3   2023-03-21 [1] CRAN (R 4.2.0)
#>  htmltools     0.5.5   2023-03-23 [1] CRAN (R 4.2.0)
#>  knitr         1.43    2023-05-25 [1] CRAN (R 4.2.0)
#>  lifecycle     1.0.3   2022-10-07 [1] CRAN (R 4.2.0)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.2.0)
#>  odbc          1.3.5   2023-06-29 [1] CRAN (R 4.2.0)
#>  pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.2.0)
#>  purrr         1.0.1   2023-01-10 [1] CRAN (R 4.2.0)
#>  R.cache       0.16.0  2022-07-21 [1] CRAN (R 4.2.0)
#>  R.methodsS3   1.8.2   2022-06-13 [1] CRAN (R 4.2.0)
#>  R.oo          1.25.0  2022-06-12 [1] CRAN (R 4.2.0)
#>  R.utils       2.12.2  2022-11-11 [1] CRAN (R 4.2.0)
#>  Rcpp          1.0.11  2023-07-06 [1] CRAN (R 4.2.0)
#>  reprex        2.0.2   2022-08-17 [1] CRAN (R 4.2.0)
#>  rlang         1.1.1   2023-04-28 [1] CRAN (R 4.2.0)
#>  rmarkdown     2.23    2023-07-01 [1] CRAN (R 4.2.0)
#>  rstudioapi    0.15.0  2023-07-07 [1] CRAN (R 4.2.0)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.2.0)
#>  styler        1.10.1  2023-06-05 [1] CRAN (R 4.2.0)
#>  vctrs         0.6.3   2023-06-14 [1] CRAN (R 4.2.0)
#>  withr         2.5.0   2022-03-03 [1] CRAN (R 4.2.0)
#>  xfun          0.39    2023-04-20 [1] CRAN (R 4.2.0)
#>  yaml          2.3.7   2023-01-23 [1] CRAN (R 4.2.0)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.2/Resources/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

@detule
Copy link
Collaborator

detule commented Aug 11, 2023

Thanks for the reports. I should be able to tackle these in the next few weeks.

@detule detule mentioned this issue Sep 1, 2023
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

SQL Server: Index local temp tables detule/odbc
3 participants
@detule @ablack3 @jatherrien