Connecting SQL Server to R

In this post I’m going to use R to retrieve some data from SQL Server.

In order to use R in conjunction with SQL Server, but in the absence of SQL Server 2016 and its soon to be incorporated R functionality, it is necessary to use a few workarounds in order to produce the desired outcome.

R is a package based platform and does not inherently communicate with other platforms unless the relevant package is installed and called. There are quite a few packages that can be used for R to talk to SQL Server, however I prefer to use the RODBC package as it is simpler to use than other packages available, and will be using it for this example.

CONNECTING SQL SERVER TO R

Step 1: Create a connection

As RODBC requires a connection to your SQL Server database you’ll have to open up the ODBC Data Source Administrator instance on the machine you’re using. Under the User DSN tab (though you could use the System DSN) click Add to create a new connection.

Select SQL Server Native Client 11.0 and Finish.

It will then open up the following screen and fill in appropriately

It will then open up with the necessary security information, but as I’m using a local version I will persist with Windows authentication.

The next screen is where you choose the database you want to connect to. By default you will just connect to the server, but if you wish to save the results from your analysis in R back to SQL Server and to the correct database it is important that you select the desired database. For this example I’m connecting to Adventure Works.

The next screen is general configuration properties and can be changed for the user’s needs

Click finish, test the connection and you’re all done for this step!

Step 2: Connecting R

For this next part we’ll be using an R client. My preferred client is R Studio as it provides a clean interface.

Now that you’ve created your connection you’ll want to use it within R. After firing up an instance of R with the RODBC package installed you will want to invoke it with the following syntax:

library(RODBC)

To bring the connection through to R you’ll need to assign a variable to it with the help of the odbcConnect function.

The format for invoking the function is as follows:

connectionstring <- odbcConnect(“some dsn”, uid = “user”, pwd = “****”)

connectionstring is the variable assigned to store the connection

odbcConnect is the function

“some dsn” is the name of your DSN connection

uid  and pwd are the User ID and password for the server, if needed

For our example using AdventureWorks on a local machine the syntax is as follows:

AdventureWorks <- odbcConnect ("AdventureWorks")

In order to see which objects are in your database you should run the following syntax:

sqlTables(AdventureWorks)

Which produces an output similar to this:

You can then begin to use your data from SQL Server in R by using the sqlQuery function to extract data.

Employee <- sqlQuery(AdventureWorks,

“SELECT * FROM HumanResources.Employee”)

The purpose of the sqlQuery function is to be able to get a specific set of data, potentially from multiple tables. If you just wish to return the contents of one table it would be better to use the sqlFetch function.

Employee <- sqlFetch(AdventureWorks,“HumanResources.Employee”)

sqlFetch returns the contents of the specified table and stores it in the assigned variable.

Connecting R to SQL Server is relatively easy and allows you to unleash the power of R on your data without employing expensive add-ons or waiting for a future SQL Server 2016 CTP to be released.