0

I want to connect R to SQL Server so I can export some R data frames as tables to SQL Server.

From a few online tutorials, I've seen they use the RODBC package, and it seems that you first need to create an ODBC name first, by going to ODBC Data sources (64-bit) > System DSN > Add > SQL Server Native Client 11.0> and then insert your specifications.

I have no idea how databases are managed, so forgive my ignorance here.. my question is: if there is already a database/server set up on SQL Server, particularly also where I want to export my R data to, do I still need to do this?

For instance, when I open Microsoft SQL Server Management Studio, I see the following:

  • Server type: Database Engine
  • Server name: example.server.myorganization.com
  • Authentication: SQL Sever Authentication
  • Login: organization_user
  • Password: organization_password

After logging in, I can access a database called "Organization_Division_DBO" > Tables which is where I want to upload my data from R as a table. Does this mean the whole ODBC shebang is already setup for me, and I can skip the steps mentioned here where an ODBC needs to be set up?

Can I instead use the code shown here:

library(sqldf)
library(odbc)
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "example.server.myorganization.com",
                 Database = "Organization_Division_DBO",
                 UID = "organization_user",
                 PWD = "organization_password")
dbWriteTable(conn = con, 
             name = "My_R_Table", 
             value = )  ## x is any data frame I have in R

I note that on this page they use a similar code to above (what is port number?) and also there is some mention "that there is also support for DSNs", so I am a little confused. Also, is there any advantage/disadvantage over using the ODBC package over the RODBC package to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mathplzfun
  • 121
  • 8
  • `odbcConnect()` requires a DSN. Have you looked into [`odbcDriverConnect()`](https://www.rdocumentation.org/packages/RODBC/versions/1.3-19/topics/odbcConnect) which can work with a connection string and thus not require a preconfigured DSN? – AlwaysLearning Jan 20 '22 at 03:03
  • `odbcConnect` is from the `RODBC` package, but you're using `dbConnect` which is from `DBI`. `DBI::dbConnect` does not require a DSN, I connect (to mssql) regularly without having a configured ODBC DSN configured formally. – r2evans Jan 20 '22 at 03:12
  • If you don't need to set port, it doesn't matter. It's a critical component of TCP/IP networking, and the default port for SQL Server (from Microsoft) is 1433. If you don't specify it, then the ODBC driver will auto-select that and all is good; the only times you need to change it are when the port is non-standard (as in a customized installation or gaining access through a tunnel such as with SSH. – r2evans Jan 20 '22 at 03:14
  • FYI, the `"SQL Server"` ODBC driver is *ancient*, and there are plenty of problems with that driver that were never (and will never be) fixed. I suggest if you can, update your system (or ask the admin to do it) to [ODBC Driver 17 for SQL Server](https://www.microsoft.com/en-us/download/details.aspx?id=56567), and also make sure you're using `odbc` version 1.3.1 or later. – r2evans Jan 20 '22 at 03:17
  • @AlwaysLearning I think I've heard a mention of "connection string" before, but am not entirely clear on what that is. Are you saying that instead of my `con <- dbConnect(....)` line, I can replace it with what's here: https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/ ? – mathplzfun Jan 20 '22 at 03:20
  • Thanks for the response @r2evans. I actually wasn't quite sure what `"SQL Server"` was in reference to, or what "driver" means in this context. It's quite possible that whatever the driver is, but I recall now that when I go to ODBC Data Source Admin and try to create anew Data source, I see three options: `ODBC Driver 13 for SQL Server`, `SQL Server`, and `SQL Server Native Client 11.0`. How do I check which one the system is currently using? – mathplzfun Jan 20 '22 at 03:24
  • Also @r2evans, just to confirm, are you saying that the code block I have pasted above should be perfectly fine, with the exception of the driver which should be updated? – mathplzfun Jan 20 '22 at 03:25
  • 1
    An advantage of using connection strings is that allegedly one connection string can be used with multiple languages, including R. If you aren't using multiple programming languages and you can get it to work with params as you've shown above, there is no advantage to switching over. Sometimes I've seen configurations that don't seem to work as params but work as a connection string, but I cannot recall much about them atm. – r2evans Jan 20 '22 at 03:25
  • 1
    If your code above works, there is no need to change it. Switching to a named DSN or to a connection string will make nothing faster, slower, enabled, disabled, etc. A connection, once made, is a connection. The newest of your versions listed is `Driver 13`, and that is also rather old. I strongly recommend switching to `Driver="ODBC Driver 17 for SQL Server"` once you have updated the driver (won't work before then). You can confirm what R/`odbc` sees with `odbc::odbcListDrivers()`. You have complete control over the driver, so if your connection parms say "SQL Server", you're using *old*. – r2evans Jan 20 '22 at 03:28
  • FYI, `sqldf` does nothing with your work here. While `sqldf` does support various DBMS backends, SQL Server is not one of them. What I'm saying here is that unless you are explicitly using `sqldf` for other non-mssql things, you do not need to load it (and it is irrelevant for this question). (The linked answer is also "wrong" in this regard.) – r2evans Jan 20 '22 at 03:30
  • @r2evans Thank you so much for your reply. this helps clear things up a lot. At this point, I haven't had the opportunity to run this on my work laptop but just wanted to make sure I was ready to go once I have the opportunity to. Also, as per your initial comment, will I also need to install the `DBI` package if I want to use the above code chunk? – mathplzfun Jan 20 '22 at 03:39
  • 1
    You need `DBI`, yes. Installing `odbc` with dependencies should install it for you, but yes you will need it. Forgive me, though, I saw `I can access` and assumed that you were already using this code and could already access the server you needed. Because of that, I removed the [tag:rodbc] tag, since this code does not use that package, this code is using just `DBI` and `odbc`. If you need to use `RODBC`, then you'll need to adjust your code (and we can put the tag back in place). – r2evans Jan 20 '22 at 04:00
  • Ah sorry for the confusion. Anyway, thank you a lot for your help and clarification. I really appreciate it! – mathplzfun Jan 20 '22 at 05:30

0 Answers0