3

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
Waldi
  • 39,242
  • 6
  • 30
  • 78
user1700890
  • 7,144
  • 18
  • 87
  • 183
  • 1
    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 Feb 22 '21 at 13:49
  • 1
    Would you like to save it to a certain schema within the database? Does it work if you run the SQL code directly? – tester Feb 22 '21 at 14:35
  • @tester, yes, my full path is this `[some_db].[some_schema].[some_table]`. When I connect to SQL Server, I do not specify any db name. I am not sure what you mean by when I run code directly. – user1700890 Feb 22 '21 at 14:55
  • 1
    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 Feb 22 '21 at 15:08
  • @tester, I updated my post to answer your question. – user1700890 Feb 22 '21 at 15:09
  • 1
    @tester `dbWriteTable` this is different package `odbc` and `DBI` if I remember correctly, I will try them as well. – user1700890 Feb 22 '21 at 15:10
  • I personnaly confirmed many times this [benchmark](https://github.com/r-dbi/odbc#benchmarks) : `DBI` is way faster than `RODBC`. Is there a specific reason why you would prefer `RODBC` to `DBI`? – Waldi Feb 22 '21 at 15:21
  • @Waldi `RODBC` is usually easier to install. – user1700890 Feb 22 '21 at 15:22
  • @user1700890 can you specify the version of SQL Server you are using? – sgdata Feb 22 '21 at 15:23
  • 1
    I see. However, installation is done once, whereas slowness in queries is every time you use it ;-) – Waldi Feb 22 '21 at 15:23
  • @sgoley How can I find out SQL Server version I am guessing it is 2012. – user1700890 Feb 22 '21 at 15:24
  • 1
    You can run `SELECT @@VERSION` in SSMS. – sgdata Feb 22 '21 at 15:35
  • @sgoley, Here is what your command returned: `Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation ` – user1700890 Feb 22 '21 at 15:41
  • This [seems related](https://stackoverflow.com/questions/23913616/rodbc-sqlsave-table-creation-problems) – Waldi Feb 22 '21 at 15:45
  • could you share the connection string you use in `odbcDriverConnect`? – Waldi Feb 22 '21 at 15:55
  • did you try to [remove the brackets](https://stackoverflow.com/a/44649996/13513328)? – Waldi Feb 22 '21 at 16:11
  • @Waldi, I added connection string and yes, I tried removing brackets with the same result. – user1700890 Feb 22 '21 at 16:16
  • 1
    OK, thanks. The link above suggests to specify `database=` in connection string to avoid problems. you can then call `sqlSave` without database in brackets. I don't know though how to specify schema. Perhaps first try without schema. – Waldi Feb 22 '21 at 16:21
  • @Waldi, db in connection string and no brackets in schema and table worked! You can posted it as the answer and get the bounty. Thank you for your help! – user1700890 Feb 22 '21 at 16:23

1 Answers1

3

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)
Waldi
  • 39,242
  • 6
  • 30
  • 78