RODBC ERROR: Could not SQLExecDirect 'CREATE T

Question

Here is the short story, and it is important to talk about the setup, I’m using RODBC and FreeTDS to connect to the SQL Azure database. Remember it is SQL Azure database (also happen to be issue for on premise SQL Server, that alarm me to post it here to ask for help or understand the real issue). First, it does not play nice with the database without a cluster key, or a key, so what need to do is addPK = T or longer version addPK = TRUE

Solution #1. two steps approach

This will help create the proper table structure, however it’ll still fail you. Don’t worry. Now what we need to do is to append the data

sqlSave(ch, USArrests, addPK = T)

It will tell you the error, then what you need to do next is to append the data

sqlSave(ch, USArrests, tablename = ‘USArrests’, append = T)

Now you ready to hail or shout hurray?!

Solution #2.

That is not me, I’m lazy, it should be done in one command

That solution does not seem to be making sense, here is the better version

sqlSave(ch, cust, tablename=”dbo.customers”, safer=F, addPK=T)

Safer = F does the trick. not sure if this is the bug from FreeTDS or RODBC sqlSave function.

http://tiggeranddraggon.tumblr.com/post/98707948606/the-unintuitive-could-not-sqlexecdirect-rodbc-error-and

Answer 1

Finally, I found the answer, which might be so obvious - USE Microsoft SQL Server ODBC Driver for Linuxhttp://www.microsoft.com/en-us/download/details.aspx?id=36437