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.

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

Temporary tables in MSSQL #2931

Closed
donaldmusgrove opened this Issue Jun 30, 2017 · 4 comments

Comments

Projects
None yet
4 participants
@donaldmusgrove

donaldmusgrove commented Jun 30, 2017

I receive the below error when attempting to create a temporary table on a MSSQL database by copying a column from an existing table into the new temporary table. Below, the error shows the underlying SQL code which does not appear correct for MSSQL (I'm no SQL pro but I use either CREATE TABLE #TempName or INTO). I can successfully copy local dataframes into temporary tables via the copy_to function, though this feature isn't very useful for my current situation.

library(tidyverse)
#> Loading tidyverse: ggplot2
#> Loading tidyverse: tibble
#> Loading tidyverse: tidyr
#> Loading tidyverse: readr
#> Loading tidyverse: purrr
#> Loading tidyverse: dplyr
#> Conflicts with tidy packages ----------------------------------------------
#> filter(): dplyr, stats
#> lag():    dplyr, stats
library(DBI)

con <- DBI::dbConnect(odbc::odbc(), "MyDB")

d1 <- tbl(con, "Device") %>%  select(Key) %>% compute(name="#keys")
#> Error: <SQL> 'CREATE TEMPORARY TABLE "#keys" AS SELECT "Key" AS "Key"
#> FROM (SELECT "Key" AS "Key"
#> FROM "Device") "tjfnbkcubs"'
#>   nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement.
@Hong-Revo

This comment has been minimized.

Show comment
Hide comment
@Hong-Revo

Hong-Revo Sep 17, 2017

Until the fix in dbplyr goes live, you can use the workaround here:

https://stackoverflow.com/a/46267726/474349

This has also been added to the dbplyr PR, although there's some questions remaining about the interface.

Hong-Revo commented Sep 17, 2017

Until the fix in dbplyr goes live, you can use the workaround here:

https://stackoverflow.com/a/46267726/474349

This has also been added to the dbplyr PR, although there's some questions remaining about the interface.

@kerry-ja

This comment has been minimized.

Show comment
Hide comment
@kerry-ja

kerry-ja Oct 23, 2017

I get an error when trying to use local temporary tables in a SQL Server database.

library(odbc)
library(DBI)
con <- DBI::dbConnect(odbc::odbc(), 
                      Driver = "SQL Server", 
                      Server = ServerName, 
                      Database = DatabaseName, 
                      Trusted_Connection = "True")

# Create 3 tables in SQL Server database; a local temporary, a global temporary and a non-temporary
dbExecute(con, "CREATE TABLE #LocalTemp (TableType varchar(255));
CREATE TABLE ##GlobalTemp (TableType varchar(255));
CREATE TABLE NonTemp (TableType varchar(255));
INSERT INTO #LocalTemp VALUES ('Local Temporary Table');
INSERT INTO ##GlobalTemp VALUES ('Global Temporary Table');
INSERT INTO NonTemp VALUES ('Non Temporary Table');
")

# Query the tables, and see what the response is
dbReadTable(con, "#LocalTemp")
dbReadTable(con, "##GlobalTemp")
dbReadTable(con, "NonTemp")

# Drop the tables
dbRemoveTable(con,"#LocalTemp")
dbRemoveTable(con,"##GlobalTemp")
dbRemoveTable(con,"NonTemp")

# Disconnect
dbDisconnect(con)

When I run the code, it works correctly for the non temporary table and the global temporary table, but not for the local temporary table. Please note that there are differences between a local temporary table and a global temporary table, and to minimize the risk of different users interfering with each other local temporary tables would be preferred.
Difference between local and global temporary tables

> # Query the tables, and see what the response is
> dbReadTable(con, "#LocalTemp")
Error: <SQL> 'SELECT * FROM "#LocalTemp"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#LocalTemp'. 
> dbReadTable(con, "##GlobalTemp")
               TableType
1 Global Temporary Table
> dbReadTable(con, "NonTemp")
            TableType
1 Non Temporary Table
> 
> # Drop the tables
> dbRemoveTable(con,"#LocalTemp")
Error: <SQL> 'DROP TABLE  "#LocalTemp"'
  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. 
> dbRemoveTable(con,"##GlobalTemp")
> dbRemoveTable(con,"NonTemp")

The same SQL works without issue using the RODBC package

kerry-ja commented Oct 23, 2017

I get an error when trying to use local temporary tables in a SQL Server database.

library(odbc)
library(DBI)
con <- DBI::dbConnect(odbc::odbc(), 
                      Driver = "SQL Server", 
                      Server = ServerName, 
                      Database = DatabaseName, 
                      Trusted_Connection = "True")

# Create 3 tables in SQL Server database; a local temporary, a global temporary and a non-temporary
dbExecute(con, "CREATE TABLE #LocalTemp (TableType varchar(255));
CREATE TABLE ##GlobalTemp (TableType varchar(255));
CREATE TABLE NonTemp (TableType varchar(255));
INSERT INTO #LocalTemp VALUES ('Local Temporary Table');
INSERT INTO ##GlobalTemp VALUES ('Global Temporary Table');
INSERT INTO NonTemp VALUES ('Non Temporary Table');
")

# Query the tables, and see what the response is
dbReadTable(con, "#LocalTemp")
dbReadTable(con, "##GlobalTemp")
dbReadTable(con, "NonTemp")

# Drop the tables
dbRemoveTable(con,"#LocalTemp")
dbRemoveTable(con,"##GlobalTemp")
dbRemoveTable(con,"NonTemp")

# Disconnect
dbDisconnect(con)

When I run the code, it works correctly for the non temporary table and the global temporary table, but not for the local temporary table. Please note that there are differences between a local temporary table and a global temporary table, and to minimize the risk of different users interfering with each other local temporary tables would be preferred.
Difference between local and global temporary tables

> # Query the tables, and see what the response is
> dbReadTable(con, "#LocalTemp")
Error: <SQL> 'SELECT * FROM "#LocalTemp"'
  nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#LocalTemp'. 
> dbReadTable(con, "##GlobalTemp")
               TableType
1 Global Temporary Table
> dbReadTable(con, "NonTemp")
            TableType
1 Non Temporary Table
> 
> # Drop the tables
> dbRemoveTable(con,"#LocalTemp")
Error: <SQL> 'DROP TABLE  "#LocalTemp"'
  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. 
> dbRemoveTable(con,"##GlobalTemp")
> dbRemoveTable(con,"NonTemp")

The same SQL works without issue using the RODBC package

@kerry-ja

This comment has been minimized.

Show comment
Hide comment
@kerry-ja

kerry-ja Oct 25, 2017

Sorry, I know this issue is marked as closed, but I still get the same errors as previously when trying to query a local temporary table in a SQL server database where the local temporary table (starts with a single #) was created in the session. I did update the packages

devtools::install_github("tidyverse/dbplyr")
devtools::install_github("rstats-db/odbc")
devtools::install_github("rstats-db/DBI")

kerry-ja commented Oct 25, 2017

Sorry, I know this issue is marked as closed, but I still get the same errors as previously when trying to query a local temporary table in a SQL server database where the local temporary table (starts with a single #) was created in the session. I did update the packages

devtools::install_github("tidyverse/dbplyr")
devtools::install_github("rstats-db/odbc")
devtools::install_github("rstats-db/DBI")
@hadley

This comment has been minimized.

Show comment
Hide comment
@hadley

hadley Oct 25, 2017

Member

@kerry-ja commenting on closed issues is generally not a good strategy. Please create a reprex (with the reprex package) and file a new issue.

Member

hadley commented Oct 25, 2017

@kerry-ja commenting on closed issues is generally not a good strategy. Please create a reprex (with the reprex package) and file a new issue.

@lock lock bot locked as resolved and limited conversation to collaborators Jun 7, 2018

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.