I've noticed that RODBC has some issues with MS SQL Server's syntax for temp
tables. For example:
> temp <- data.frame(x = 1:10)
> sqlSave(conn, temp, "#rtemp")
Error in sqlColumns(channel, tablename) :
'#rtemp': table not found on channel
> sqlQuery(conn, "create table #rtemp (x integer)")
[1] "42S01 2714 [Microsoft][ODBC SQL Server Driver][SQL Server]There is already
an object named '#rtemp' in the database."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'create table #rtemp (x integer)'"
> sqlSave(conn, temp, "#rtemp", append = TRUE)
Error in sqlSave(conn, temp, "#rtemp", append = TRUE) :
42S01 2714 [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an
object named '#rtemp' in the database.
[RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE "#rtemp" ("rownames"
varchar(255), "x" int)'
> sqlQuery(conn, "select * from #rtemp")
[1] rownames x
<0 rows> (or 0-length row.names)
> for (i in 1:nrow(temp)) { sqlQuery(conn, sprintf("insert into #rtemp (x)
> values (%d)", temp$x[i])) }
> sqlQuery(conn, "select * from #rtemp")
rownames x
1 NA 1
2 NA 2
3 NA 3
4 NA 4
5 NA 5
6 NA 6
7 NA 7
8 NA 8
9 NA 9
10 NA 10
Is there any way to get functions like 'sqlSave' working?
Thanks.
- Elliot
Elliot Joel Bernstein, Ph.D.
Grantham, Mayo, Van Otterloo & Co. LLC
225 Franklin Street
Boston, MA 02110
Office: 617-880-8972
[[alternative HTML version deleted]]
_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-db
