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@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, rip...@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
______________________________________________
R-help@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.
