Why doesn't RODBC load a DataFrame on SQL Server?

Question

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

Attempt to load data frame:

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

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

.. alternatively creating a table first and then adding 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?

Answer 1

If I add parentheses, I also get an error message.

If I use the database connection string to make sure I enter the correct database (and not the wizard) and execute the sqlSave(con, df, tablename='dbo.MyTable4', rownames=F) or sqlSave(con, df, tablename='MyTable5', rownames=F) , it works.

Answer 2

When connecting to Microsoft SQL Server, it is important to use odbcDriverConnect instead of odbcConnect to execute sqlSave , etc. Only odbcDriverConnect allows you to specify a specific database in the connection string.

Answer 3

RODBC looks at the default folder to connect to the ODBC server to find out if you have write permissions (even if you intend to use a subdirectory). If you do not have basic access rights, then this will not work.

I needed to create two connections inside R, one for reading from the main and one for writing to my temporary directory. They were established by creating two server connections using my ODBC administration on the local computer (in Win7):

-One that the default is a write-protected master server directory, which I use for read-only output.

-One that the server directory is used by default, to which I have write / delete permissions.

In other words, your problem is solved by changing the ODBC connection to the machine and pointing R to a new connection to the server that you will make (the one that defaults to your write-enabled table.