Error with sqlSave

Question

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

Oujda

Give us a reproducible example to work on. – Thorough

Answer 1

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)

Typify

Answer 2

I had the same problem. What I realized is that by default sqlSave would create the table in the 'Master' schema. I launched the ODBC Data Source Administrator and changed the default database and selected the desired database and it worked.

Ezarra

Answer 3

I have struggled witrh same issue with you. I can call odbcQuery to insert data line by line. However, my data.frame has tens of miliions of line. It's kind of to oslow by insert. If your data set is not large, you may try it.

Teenateenage

Answer 4

The problem is that you wrote the tablename parameter as "[testsFelix].[dbo].[TREB]" when you have to write it as "[dbo].[TREB]" ommiting tha database.

You have to change the database of your odbc channel to the one you are interested. In the odbc administrator in Microsoft. Maybe the problem is that the default database is one different than [testsFelix]

Therefore the solution that I had to your problem was

1. change database of your channel to [testsFelix], in Microsoft through the odbc administrator

2. tablename parameter in sqlSave does not expect the database, therfore you have to write as [schema].[tablename] sintaxis

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

By the way. In my case is faster to insert values in blocks of 1000 observations.

try the trick:

vals = paste0("('", b$Field1   , "','",
                    b$Field2   , "','",
                    b$Field3   , "','",
                    b$lastField, "')", collapse = ",")


sqlQuery(channel, 
         query = paste0("INSERT INTO [testsFelix].[dbo].[TREB] 
                         values", vals), as.is = TRUE)

Hooper

Answer 5

Please try this

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

What I found is that the Excel will add a "_" in front of the default filename, if you add this to the filename, Excel will find the table.

Adabelle

From the error message, it seems the connection is to an SQL server, not to an Excel file. – Kendakendal

Answer 6

You have to remove your brackets ([]), and then it should run fine.

Baisden