RODBC error: SqlSave unable to append to table

Question

we hit to an error with RODBC and SqlSave command. We are a bit confused what to do since the same SqlSave command works when data that we are trying to save to Sybase database is small (~under 10.000 rows). When trying to save bigger data (~200.000 rows) saving process starts without any problems but it crashes after few thousand rows is saved. Then we hit to this error message “unable to append to table..”

We use this kind of code:

library(RODBC)

channel <- odbcConnect("linfo-test", uid="DBA", pwd="xxxxxx", believeNRows=FALSE)

sqlSave(channel=channel, dat=matkat, tablename = "testitaulu", append = TRUE)

odbcClose(channel)

If someone has any idea why this happens only with bigger data and how we could fix this, we would be extremely grateful. We are lacking ideas ourselves.

Answer 1

sqlSave with append=TRUE pretty much never works. You will have to explicitly write an SQL INSERT INTO statement, which is unfortunate. Sorry for the bad news.

Answer 2

sqlSave is working but you have to be really careful with it. You need to remember:

• in R column names of your data frame and sql server table have to match EXACTLY and remember that R is case sensitive, even leading space or space at the end can make a difference
• make sure that your data types in R and sql table are matching
• that you are not missing any column to insert( even it is default in sql)
• connection have to be set up properly, if sql user doesn't have permission to read and write in a destination table(used in sqlSave) it can also fail