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
– Colin H, Commented Feb 22, 2021 at 13:49
Would you like to save it to a certain schema within the database? Does it work if you run the SQL code directly? — tester
– tester, Commented Feb 22, 2021 at 14:35
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, Commented Feb 22, 2021 at 15:08
@tester dbWriteTable this is different package odbc and DBI if I remember correctly, I will try them as well. — user1700890
– user1700890, Commented Feb 22, 2021 at 15:10
I see. However, installation is done once, whereas slowness in queries is every time you use it ;-) — Waldi
– Waldi, Commented Feb 22, 2021 at 15:23

Waldi · Accepted Answer · 2021-02-22 16:46:11Z

3

+200

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)

edited Feb 22, 2021 at 16:46

answered Feb 22, 2021 at 16:31

Waldi

41.6k6 gold badges38 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related