{"action":"created","issue":{"url":"https://api.github.com/repos/r-dbi/odbc/issues/127","repository_url":"https://api.github.com/repos/r-dbi/odbc","labels_url":"https://api.github.com/repos/r-dbi/odbc/issues/127/labels{/name}","comments_url":"https://api.github.com/repos/r-dbi/odbc/issues/127/comments","events_url":"https://api.github.com/repos/r-dbi/odbc/issues/127/events","html_url":"https://github.com/r-dbi/odbc/issues/127","id":268713718,"node_id":"MDU6SXNzdWUyNjg3MTM3MTg=","number":127,"title":"Issue creating local temporary table in SQL Server dB using Select Into SQL statement","user":{"login":"kerry-ja","id":25394804,"node_id":"MDQ6VXNlcjI1Mzk0ODA0","avatar_url":"https://avatars0.githubusercontent.com/u/25394804?v=4","gravatar_id":"","url":"https://api.github.com/users/kerry-ja","html_url":"https://github.com/kerry-ja","followers_url":"https://api.github.com/users/kerry-ja/followers","following_url":"https://api.github.com/users/kerry-ja/following{/other_user}","gists_url":"https://api.github.com/users/kerry-ja/gists{/gist_id}","starred_url":"https://api.github.com/users/kerry-ja/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kerry-ja/subscriptions","organizations_url":"https://api.github.com/users/kerry-ja/orgs","repos_url":"https://api.github.com/users/kerry-ja/repos","events_url":"https://api.github.com/users/kerry-ja/events{/privacy}","received_events_url":"https://api.github.com/users/kerry-ja/received_events","type":"User","site_admin":false},"labels":[{"id":407723716,"node_id":"MDU6TGFiZWw0MDc3MjM3MTY=","url":"https://api.github.com/repos/r-dbi/odbc/labels/bug","name":"bug","color":"e02a2a","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":6,"created_at":"2017-10-26T10:50:10Z","updated_at":"2018-06-11T18:11:26Z","closed_at":null,"author_association":"NONE","body":"### Issue Description and Expected Result\r\nWhen I try to create a local temporary table in a SQL Server database using the Select into SQL statement, I would expect that the table would be created, and could later be queried or read or dropped, as can be done for a global temporary table, or a non-temporary table, instead an error is generated when trying to read  or query or drop the table \r\n\r\n### Database\r\nMicrosoft SQL Server 2014 (SP1-CU5) (KB3130926) - 12.0.4439.1 (X64) \r\n Feb 15 2016 12:12:43 \r\n Copyright (c) Microsoft Corporation\r\n Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )\r\n\r\n\r\n### Reproducible Example\r\n``` r\r\nserverName <- \"Server Name\"\r\ndBname <- \"Database Name\"\r\n\r\nlibrary(odbc)\r\nlibrary(DBI)\r\nlibrary(dbplyr)\r\n\r\ncon <-\r\n  dbConnect(odbc::odbc(),\r\n            driver = \"{SQL Server}\",\r\n            server = serverName,\r\n            database = dBname)\r\n\r\n# Create 3 tables in SQL Server database; a local temporary, a global temporary and a non-temporary \r\n# using  The SQL SELECT INTO Statement where the table type is determined by number of pound signs in table name (none for non-temp table)\r\ndbExecute(con, \"Select 'Local Temp Table' as TableType into #LocalTemp;\r\n          Select 'Global Temp Table' as TableType into ##GlobalTemp;\r\n          Select 'Non-Temp Table' as TableType into NonTemp;\r\n          \")\r\n#> [1] 1\r\n\r\n# Query the tables, and see what the response is\r\ndbGetQuery(con, \"Select * from #LocalTemp\")\r\n#> Error: <SQL> 'Select * from #LocalTemp'\r\n#>   nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#LocalTemp'.\r\ndbGetQuery(con, \"Select * from ##GlobalTemp\")\r\n#>           TableType\r\n#> 1 Global Temp Table\r\ndbGetQuery(con, \"Select * from NonTemp\")\r\n#>        TableType\r\n#> 1 Non-Temp Table\r\n\r\n# Read the tables, and see what the response is\r\ndbReadTable(con, \"#LocalTemp\")\r\n#> Error: <SQL> 'SELECT * FROM \"#LocalTemp\"'\r\n#>   nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#LocalTemp'.\r\ndbReadTable(con, \"##GlobalTemp\")\r\n#>           TableType\r\n#> 1 Global Temp Table\r\ndbReadTable(con, \"NonTemp\")\r\n#>        TableType\r\n#> 1 Non-Temp Table\r\n\r\n# Drop the tables\r\ndbRemoveTable(con,\"#LocalTemp\")\r\n#> Error: <SQL> 'DROP TABLE  \"#LocalTemp\"'\r\n#>   nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table '#LocalTemp', because it does not exist or you do not have permission.\r\ndbRemoveTable(con,\"##GlobalTemp\")\r\ndbRemoveTable(con,\"NonTemp\")\r\n\r\n# Disconnect\r\ndbDisconnect(con)\r\n\r\n# See what the session info is\r\ndevtools::session_info()\r\n#> Session info -------------------------------------------------------------\r\n#>  setting  value                       \r\n#>  version  R version 3.4.2 (2017-09-28)\r\n#>  system   x86_64, mingw32             \r\n#>  ui       RTerm                       \r\n#>  language (EN)                        \r\n#>  collate  English_United States.1252  \r\n#>  tz       America/New_York            \r\n#>  date     2017-10-26\r\n#> Packages -----------------------------------------------------------------\r\n#>  package    * version    date       source                            \r\n#>  assertthat   0.2.0      2017-04-11 CRAN (R 3.4.0)                    \r\n#>  backports    1.1.1      2017-09-25 CRAN (R 3.4.1)                    \r\n#>  base       * 3.4.2      2017-09-28 local                             \r\n#>  bindr        0.1        2016-11-13 CRAN (R 3.4.0)                    \r\n#>  bindrcpp     0.2        2017-06-17 CRAN (R 3.4.0)                    \r\n#>  bit          1.1-12     2014-04-09 CRAN (R 3.4.0)                    \r\n#>  bit64        0.9-7      2017-05-08 CRAN (R 3.4.0)                    \r\n#>  blob         1.1.0      2017-06-17 CRAN (R 3.4.0)                    \r\n#>  compiler     3.4.2      2017-09-28 local                             \r\n#>  datasets   * 3.4.2      2017-09-28 local                             \r\n#>  DBI        * 0.7-12     2017-10-24 Github (rstats-db/DBI@f8c97f2)    \r\n#>  dbplyr     * 1.1.0.9000 2017-10-26 Github (tidyverse/dbplyr@3bdee6a) \r\n#>  devtools     1.13.3     2017-08-02 CRAN (R 3.4.1)                    \r\n#>  digest       0.6.12     2017-01-27 CRAN (R 3.4.0)                    \r\n#>  dplyr        0.7.4      2017-09-28 CRAN (R 3.4.2)                    \r\n#>  evaluate     0.10.1     2017-06-24 CRAN (R 3.4.1)                    \r\n#>  glue         1.1.1      2017-06-21 CRAN (R 3.4.0)                    \r\n#>  graphics   * 3.4.2      2017-09-28 local                             \r\n#>  grDevices  * 3.4.2      2017-09-28 local                             \r\n#>  hms          0.3        2016-11-22 CRAN (R 3.4.0)                    \r\n#>  htmltools    0.3.6      2017-04-24 Github (rstudio/htmltools@4fbf990)\r\n#>  knitr        1.17       2017-08-10 CRAN (R 3.4.1)                    \r\n#>  magrittr     1.5        2014-11-22 CRAN (R 3.4.0)                    \r\n#>  memoise      1.1.0      2017-04-21 CRAN (R 3.4.0)                    \r\n#>  methods    * 3.4.2      2017-09-28 local                             \r\n#>  odbc       * 1.1.3.9000 2017-10-24 Github (rstats-db/odbc@b3d159a)   \r\n#>  pkgconfig    2.0.1      2017-03-21 CRAN (R 3.4.0)                    \r\n#>  R6           2.2.2      2017-06-17 CRAN (R 3.4.0)                    \r\n#>  Rcpp         0.12.13    2017-09-28 CRAN (R 3.4.1)                    \r\n#>  rlang        0.1.2      2017-08-09 CRAN (R 3.4.1)                    \r\n#>  rmarkdown    1.6        2017-06-15 CRAN (R 3.4.0)                    \r\n#>  rprojroot    1.2        2017-01-16 CRAN (R 3.4.0)                    \r\n#>  stats      * 3.4.2      2017-09-28 local                             \r\n#>  stringi      1.1.5      2017-04-07 CRAN (R 3.4.0)                    \r\n#>  stringr      1.2.0      2017-02-18 CRAN (R 3.4.0)                    \r\n#>  tibble       1.3.4      2017-08-22 CRAN (R 3.4.1)                    \r\n#>  tools        3.4.2      2017-09-28 local                             \r\n#>  utils      * 3.4.2      2017-09-28 local                             \r\n#>  withr        2.0.0      2017-07-28 CRAN (R 3.4.1)                    \r\n#>  yaml         2.1.14     2016-11-12 CRAN (R 3.4.0)\r\n```\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/r-dbi/odbc/issues/comments/396335070","html_url":"https://github.com/r-dbi/odbc/issues/127#issuecomment-396335070","issue_url":"https://api.github.com/repos/r-dbi/odbc/issues/127","id":396335070,"node_id":"MDEyOklzc3VlQ29tbWVudDM5NjMzNTA3MA==","user":{"login":"aryoda","id":11374410,"node_id":"MDQ6VXNlcjExMzc0NDEw","avatar_url":"https://avatars1.githubusercontent.com/u/11374410?v=4","gravatar_id":"","url":"https://api.github.com/users/aryoda","html_url":"https://github.com/aryoda","followers_url":"https://api.github.com/users/aryoda/followers","following_url":"https://api.github.com/users/aryoda/following{/other_user}","gists_url":"https://api.github.com/users/aryoda/gists{/gist_id}","starred_url":"https://api.github.com/users/aryoda/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aryoda/subscriptions","organizations_url":"https://api.github.com/users/aryoda/orgs","repos_url":"https://api.github.com/users/aryoda/repos","events_url":"https://api.github.com/users/aryoda/events{/privacy}","received_events_url":"https://api.github.com/users/aryoda/received_events","type":"User","site_admin":false},"created_at":"2018-06-11T18:11:26Z","updated_at":"2018-06-11T18:11:26Z","author_association":"CONTRIBUTOR","body":"Adding \"ConnectionPooling=false;\" to the connection string was just my guess and seems to be wrong.\r\n\r\nSetting \"PerfMon\" to \"Disable\" does **not** disable the connection pooling but only the performance monitoring.\r\n\r\nTo disable the connection pool you have to **double click** on the driver name (thanks for hiding this, M$ ;-)\r\n\r\n![disable_odbc_connection_pooling](https://user-images.githubusercontent.com/11374410/41249163-9543a274-6db3-11e8-85be-c93914483300.png)\r\n"}}
{"actor":{"display_login":"aryoda"}}