Connecting R to Microsoft SQL Server

Connecting R to SQL Server to pull data from a SQL Server data warehouse or data mart is something you may want to consider if you need to do advanced statistical computing.

Assuming you have R already installed, the prerequisite steps are first you have to download and install the RODBC package.  This was done by using the menu options found under the Packages menu in the Rgui.   The second thing to do is create an ODBC data source.  I created a system data source for SQL Server.  I configured my server and default database, making sure to point the default database to the database I wanted to connect to.  In this case I was connecting to the CatchAll database.  I did not feel very creative so I called the ODBC connection the same name as the database.

The first line calls the RODBC package that supports ODBC calls.

The second line creates the connection using your ODBC connection your created previously.

The third line runs your SQL query. You can query a table or a view.  The output is sent to a data frame called “dataframe”.

Finally in the fourth line the connection is closed and in the fifth line I displayed an average of the price field in my data frame to show that in fact the dataframe has been populated.