R and MSSQL - communication with temp tables (table not found on channel)

Question

I'm trying to update global temp table using RODBC in a following way:

library(RODBC)
channel <- odbcConnect("RDataSource", uid = "user", pwd = "password")    
query <- "select * from ##TempTable"
table_data <- sqlQuery(channel, query)
# data frame creation
sqlUpdate(channel, data_frame, index = "id", verbose = TRUE, tablename = "##TempTable")

Select query executes well, but sqlUpdate is failed with error message: "Error in odbcTableExists(channel, tablename) : ‘##TempTable’: table not found on channel"

I suppose that the reason of this error may be connected with using of '#' in the name of temp tables.

UPD: I'm getting the same error with sqlSave function. This error occures only when I'm creating temp table, everything is ok with usual SQL tables. Global temp table is creating before the calling of R code.

So, is there any way to communicate with temp tables in MSSQL database using R functions such as sqlSave() and sqlUpdate()?

it might be the characters are throwing it off, it also might be wanting a schema name. I'd add in schema = "<schemaname>" into your command and see if that helps. you might also try changing the name of the table to something more sql friendly ;) — ike
In SQL Server, double hash prefixed table names are global temp tables that are created during a connection of any user and deletes when all users disconnect. Did you or any user create this ##TempTable prior to running R code during concurrent instances? — Parfait
Yes, in my case global temp table is created before R script is called to execute. Also, I can update this table by calling sqlQuery() function, but sqlUpdate() or sqlSave() functions are failing to execute. — floyd

floyd floyd · Accepted Answer · 2017-04-20T12:29:02

I finally found that the cause of this error was in the settings of ODBC Data Source. It seems that communication with temp tables using R functions such as sqlSave or sqlUpdate requires the default database in ODBC data source to be set to 'tempdb'. So, now I'm able to use sqlSave() function to insert values in temp table. This function in fact has much better performance than using sqlQuery function with the direct 'INSERT' query as a parameter.

R and MSSQL - communication with temp tables (table not found on channel)

1 Answers