Search code examples
sql-serverrrodbc

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

Solution

  • 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)