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

Answer 1

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)