sqlUpdate returns error "[RODBC] Failed exec in Update" when updating table read from DB

Question

I'm creating a table and writing it to a database with RODBC. No problems.

First I tried updating the table in R and writing the updates back line by line using sqlUpdate in the same R script and the same R session as initialising it. No problems.

But when I try to split this into two scripts (one to initialise the table and one to read it back into R, calculate updates and write the updates to the database), I get the following error (the import and updates work fine, it's just writing it back to the DB that fails):

[RODBC] Failed exec in Update

The code I use to update is the following (which worked when the table was in my R session and the initialisation and the updates were in the same script):

conOut <- odbcConnect("myDB", uid = "myID", pwd = "myPwd", believeNRows = T)

sqlUpdate(conOut, myTable)

odbcClose(conOut)

Am I just missing something really obvious like the table being converted to a wrong format when it is imported from the database? Or is there something else I can do to make it work?

Cheers.

Stretcherbearer

Answer 1

I found a work-around, although it doesn't actually answer my initial question:

Instead of using sqlUpdate, I pick out the updated values from the table in R and use sqlQuery with an update statement in which I set all the relevant entries to the new values:

observedString <- paste("[Observed] = ", obs)
differenceString <- paste("[Difference] = ", myTable$Difference[n])
shapiroString <- paste("[pValueShapiroWilks] = ", myTable$pValueShapiroWilks[n])
...
clauseString <- paste0("rownames = '", myTable$rownames[n], "'")    

updateString <- paste0("UPDATE [myDB].[dbo].[myTable]
   SET ", 
  observedString, ", ", 
  differenceString, ", ",
  shapiroString, ", ",
  ljungBoxString, ", ",
  adfString, ", ", 
  warningString, ", ", 
  changeString, 
 " WHERE " , clauseString)

conOut <- odbcConnect("myDB", uid = "myID", pwd = "myPwd", believeNRows = T)

sqlQuery(conOut, updateString)

This works perfectly.

However, I'd still like to know why the sqlUpdatecommand doesn't work...

Stretcherbearer