Connecting to MS SQL Server from R

Introduction

I was asked at work to do some data analysis (sorry can’t tell ya what it is ;)). And I realised that it was going to be a nightmare dumping the data into Excel doing analysis in Excel or writing a whole bunch of SQL queries, querying the same tables/datasets.

So I decided to write one big query with multiple joins and then do my analysis with R.

Libraries for Connecting to MS SQL Server

You will need to install the "RODBC" and "dplyr" packages in R with the following commands:

install.packages("RODBC")
install.packages("dplyr")

Using the packages in R

Whether you’re planning to use it in R Markdown file or as an R script you will need to include the packages before using them.

library("RODBC")
library("dplyr")

I used the following line of code because I do not have a DSN set-up on my work laptop.

conn <- odbcDriverConnect('driver={SQL Server};server=sqlserver;database=proddb;trusted_connection=true')

Remember to change the server and database part of the string to match your configuration.

To create dataset with your select statement you will need to use the sqlQuery command:

data <- sqlQuery(conn, "SELECT * FROM dbo.invoices;")

Now you have your dataset ready for some analysis.

I’m probably gonna use it once and then uninstall it afterwards :D. Thanks for reading.