Connecting to SQL Server using R

When analyzing data in R it is often best to source data directly, and because so much data exists in rational databases such as SQL Server it is important that we are able to connect to this data without first exporting it to a common format like CSV only to import that data into R. RODBC is a package that allows access to databases using an ODBC interface. To use RODBC you can connect using an ODBC connection as documented here or you can connect using a connection string as seen in the code snippet below.


#install.packages("RODBC")

library(RODBC)

dbConn <-  odbcDriverConnect('driver={SQL Server};server=localhost;database=MyDatabase;trusted_connection=true')

tableList <- sqlQuery(dbConn, 'select * from INFORMATION_SCHEMA.TABLES')

close(dbConn)

In this example, I have used a trusted connection but you would be able to provide a UserName and password (for more on connection strings make sure to check out connectionstrings.com).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s