Error with sqlSave

Question

I sqlSave to add my matrix B , which looks like this:

 Noinscr 88877799 45645687 23523521 45454545 

into the 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 he does not want to erase the table, even if append=TRUE is, I tried to erase the SQL table and run the same code again.

I get the following error:

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

So, I am confused when I want to add R, says that it is impossible because there is a table, and when there is no table, R says that it cannot put information in it because the table is not there. I went to SQL to make sure nothing happened, but I saw that R created a table with the correct column name (Noinscr), but the table is empty.

Please tell me what I'm doing wrong. thank you

Answer 1

I found this googling post for a similar problem. The problem recurred after restarting R , as well as rebooting the system. I narrowed the problem down to the database by opening a new connection to another database and writing it using sqlSave .

Oddly enough, the problem with the original database was fixed by opening and closing it with R :

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

After that, the following test worked 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, since my initial (non) solution was to recreate the database)

Answer 2

I had the same problem. I realized that by default sqlSave will create a table in the wizard schema. I ran the ODBC Data Source Administrator and changed the default database and selected the right database and it worked.

Answer 3

I struggled with the same problem with you. I can call odbcQuery to insert data row by row. However, my data.frame has tens of millions of rows. This is a kind of oslow insert. If your data set is small, you can try it.

Answer 4

Please, try

 sqlSave(channel, b, "_b", append = TRUE, rownames = FALSE, colnames = FALSE, safer = TRUE, fast = FALSE) 

I found that Excel will add "_" in front of the default file name, if you add it to the file name, Excel will find the table.

Answer 5

You must remove your brackets ([]) and then it should work fine.