Insert R dataframe into SQL (RODBC) - error table not found

Question

I would like to drop my whole dataframe from R preferably using RODBC with sqlSave statement (not sqlQuery). Here is my sample code.

library(RODBC)
myconn <- odbcDriverConnect("some connection string")
mydf <- data.frame(col_1 = c(1,2,3), col_2 = c(2,3,4))
sqlSave(myconn, mydf, tablename = '[some_db].[some_schema].[my_table]',  append = F, rownames = F,  verbose=TRUE)
odbcClose(myconn)

After I execute it, I get back error message:

Error in sqlColumns(channel, tablename) : ‘my_table’: table not found on channel

When I check in SQL Server, an empty table is present.

If I run the same code again, I get error message:

Error in sqlSave(myconn, mydf, tablename = "[some_db].[some_schema].[my_table]", : 42S01 2714 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'my_table' in the database. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [some_db].[some_schema].[my_table] ("col_1" float, "col_2" float)'

Any suggestions on how to troubleshoot?

UPDATE

In SSMS I can run the following commands successfully:

CREATE TABLE [some_db].[some_schema].[my_table] (
    test int
);
drop table [some_db].[some_schema].[my_table]

Here are details of connection string:

Driver=ODBC Driver 17 for SQL Server; Server=someserveraddress; Uid=user_login; Pwd=some_password

I'm not able to recreate the issue; this code works for me on my environment. Is it possible there's a permissions issue with the SQLServer database you're writing to? Perhaps try a different database or specify the database explicitly in the ODBC connection? — Colin H
Would you like to save it to a certain schema within the database? Does it work if you run the SQL code directly? — tester
Whre do you specify the schema you want do write to? I'm asking because I had similar problems when trying to insert a table in our SQL database using RPostgreSQL. In the end I went for writing the table to [some_db] using dbWriteTable and then moving it to [some_schema] with an SQL query directly, because nothing else worked and it turned out that the driver was the issue. Hence, I'd try to check if it works using pure SQL first. — tester
@tester dbWriteTable this is different package odbc and DBI if I remember correctly, I will try them as well. — user1700890
I see. However, installation is done once, whereas slowness in queries is every time you use it ;-) — Waldi

Waldi Waldi · Accepted Answer · 2021-02-22T16:31:46

To avoid the error, you could specify the database in the connection string:

Driver=ODBC Driver 17 for SQL Server; Server = someserveraddress; database = some_db; Uid = user_login; Pwd = some_password

and avoid using brackets:

sqlSave(myconn, mydf, tablename = 'some_schema.my_table',  append = F, rownames = F,  verbose=TRUE)

Insert R dataframe into SQL (RODBC) - error table not found

1 Answers