[R] Temporary tables with Microsoft SQL?

[Prof Brian Ripley]

This is an issue for the R-sig-db list (as was your other posting).

RODBC is written to use standard SQL, and Microsft often does not use 
standard SQL, as in table names like #x.

The most productive thing to do would be to modify RODBC to handle the 
Microsoft peculiarities you want and feed the changes back to the 
maintainer (mw, at present).  That is how various of those 
peculiarities have been added in the past.

On Sat, 28 Feb 2009, Andrew Ziem wrote:

> 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
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595