[R] Temporary tables with Microsoft SQL?

[Andrew Ziem]

I can create a temp table with MySQL and R DBI[1], but I don't see how
to do the same with Microsoft SQL 2005 and RODBC.  R 2.8.1 creates the
table, but then it can never see it.  I'm looking to avoid replacing
the convenience functions like sqlSave().

[1] https://stat.ethz.ch/pipermail/r-help/2009-March/190033.html

> library(RODBC)
> channel <- odbcDriverConnect("driver=SQL Server;server=foo;Initial Catalog=bar;Integrated Security=SSPI;")
> x<-data.frame(1:10)
> colnames(x) <-c("x")

> sqlSave(channel, x, "#x", verbose=TRUE)
Query: CREATE TABLE "#x"  ("rownames" varchar(255), "x" int)
Error in sqlColumns(channel, tablename) :
  ‘#x’: table not found on channel

> sqlSave(channel, "#x", verbose=TRUE) #repeat
Query: CREATE TABLE "#x"  ("rownames" varchar(255), "x" int)
Error in sqlSave(db_i3_eic, x, "#x", verbose = TRUE) :
  [RODBC] ERROR: Could not SQLExecDirect
42S01 2714 [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named '#x' in the database.

> sqlSave(channel, "x", verbose=TRUE) # try without hash
Query: CREATE TABLE "x"  ("rownames" varchar(255), "x" int)
Error in sqlSave(db_i3_eic, x, "x", verbose = TRUE) :
  [RODBC] ERROR: Could not SQLExecDirect
42000 262 [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE
permission denied in database 'bar'.



Andrew