Why won't RODBC upload a dataframe to SQL Server?

Question

library(RODBC)
con <- odbcDriverConnect("driver=SQL Server; server=name")
df <- data.frame(a=1:10, b=10:1, c=11:20)

Trying to upload the dataframe:

sqlSave(con, df, tablename='[MyDatabase].[MySchema].[MyTable]', rownames=F)

>Error in sqlColumns(channel, tablename) : ‘MyDatabase.MySchema.MyTable’: table not found on channel

..alternatively creating the table first and then appending to it:

cmd <- "create table [MyDatabase].[MySchema].[MyTable] ([a]  int, [b] int, [c] int)"
sqlQuery(con, cmd)

sqlSave(con, df, tablename='[MyDatabase].[MySchema].[MyTable]', rownames=F, append=T)

>Error in sqlSave(con, df, tablename = "MyTable", rownames = F, : 42S01 2714 [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named MyDatabase.MySchema.MyTable in the database. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE MyDatabase.MySchema.MyTable ("a" int, "b" int, "c" int)'

What am I doing wrong?

Macaco

Do you have sufficient permissions to create tables on the SQL server? – Vincents

The code works for me. Maybe a problem with the default database or the default schema. Does print(con) give the right database? And you can add the schema with the table name. – Guajardo

@Guajardo - Interresting, I've edited my answer to include how I actually refrence MyTable (with database name and a schema). print(con) does'nt give me any information on any database, only that it is the correct server. – Macaco

If I don't specify a database in my connection string I get the same error. Try adding ...;database=mydatabase to the connection string. – Guajardo

Yes, I can add the database in the connection string and then I can just use [MySchema].[MyTable] in the query. The problem "table already exists/does not exist" is still the same, though. – Macaco

if I use a connection string with the database and execute the statement "sqlSave(con, df, tablename='dbo.MyTable4', rownames=F)" or "sqlSave(con, df, tablename='MyTable5', rownames=F)" it works. If I add brackets I also get an error. – Guajardo

Can't believe that the brackets were the issue this whole time! I'll accept that if you answer it.. – Macaco

Could somebody comment on the error message. Why there is "Create" in the error, when append is true! – Monahan

Answer 1

If I add brackets I also get an error.

If I use a connection string with the database to make sure that I am in the correct database (not master) and execute the statement sqlSave(con, df, tablename='dbo.MyTable4', rownames=F) or sqlSave(con, df, tablename='MyTable5', rownames=F) it works.

Guajardo

I'm having the same problem. Could you give me an example of the connection string you used? For me, while odbcConnect() works, if I put my string in the format you used above, I get the following: odbcDriverConnect("Driver=SQL Server; Server=xx; Database=xx; Uid=xx; Pwd=xx") [RODBC] ERROR: state IM003, code 2069604672, message [iODBC][Driver Manager]Specified driver could not be loaded – Chiliad

Answer 2

When connecting to a Microsoft SQL Server, it is essential to use odbcDriverConnect instead of odbcConnect in order to perform sqlSave statements etc. Only odbcDriverConnect allows to specifiy a specific database in the connection string.

Gallon

After banging my head against the wall for a bit your solution in conjunction with Henrico's finally worked for me: 1) Connect with odbcDriverConnect so that I was able to specify a specific database odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=YOURSERVERHERE;database=YOURDBHERE;trusted_connection=yes;") then make sure I didn't have brackets in the sqlSave statement, as suggested in the accepted answer. Thank you! – Unclose

Answer 3

RODBC looks at the default folder for the ODBC server connection to see if you have write permissions there (even if you are going for a subdirectory). If you don't have master permissions, then it fails.

I had to create two connections within R, one for reading from the master and one for writing to my temp directory. These were set by creating two server connections using my local computer's ODBC Administration (within Win7):

-One that defaults to the write-protected master server directory and that I use to pull read-only data.

-One that defaults to the server directory that I have write/drop permissions to.

In other words, your problem is solved by changing your machine's ODBC connection and having R point to the new server connection you will make (the one that defaults to your write-permissioned table.

Sapheaded