Felixthecat Felixthecat - 2 months ago 19
R Question

Error with sqlSave

I'm fighting with

sqlSave
to add my matrix
B
that looks like this:

Noinscr
88877799
45645687
23523521
45454545


to an SQL table.

so I run the following command:

sqlSave(channel, b, "[testsFelix].[dbo].[TREB]", append = TRUE,
rownames = FALSE, colnames = FALSE, safer = TRUE, fast = FALSE)


and I get the following error:

Erreur dans sqlSave(channel, b, "[testsFelix].[dbo].[TREB]", append = TRUE, :
42S01 2714 [Microsoft][SQL Server Native Client 10.0][SQL Server]
There is already an object named 'TREB' in the database.
[RODBC] ERROR: Could not SQLExecDirect
'CREATE TABLE [testsFelix].[dbo].[TREB] ("Noinscr" int)'


Seeing that it didn't want to erase the table, even if
append=TRUE
is there, I've tried to erase my SQL table and ran the same code again.

I get the following error:

Erreur dans sqlColumns(channel, tablename) :
‘[testsFelix].[dbo].[TREB]’: table not found on channel


So I'm confused, when I want to append R says it can't because the table is there and when the table is not there, R says it can't put info in it because the table is not there. I went into SQL to verify that nothing happened, but I saw that R had created the table with the right Column Name (Noinscr) but the table is empty.

Please tell me what I am doing wrong.
Thank you

Answer

I found this post googling for a similar problem. The problem persisted after restarting R, as well as a system re-boot. I narrowed the problem down to the database, by opening a new connection to different database, and writing to that using sqlSave.

Weirdly, the problem with the original database was corrected by opening and closing it using R:

DBchannel <- odbcConnectAccess(access.file = "C:/myPath/Data.mdb")
odbcClose(DBchannel)

After doing this, the following test worked just fine:

require(RODBC)
dd <- data.frame('normal' = rnorm(100), 'uniform' = runif(100))
DBchannel <- odbcConnectAccess(access.file = "C:/myPath/Data.mdb")
sqlSave(DBchan, dd, tablename='testtable')
odbcClose(DBchannel)

(which is nice, as my initial (non-)solution was to re-build the database)