Connecting to SQL Server with R using RJDBC

Download the Microsoft from here

Save the files to a convenient location: I chose C:\jdbc\sqljdbc_4.0\

Many posts show the class name as “com.microsoft.jdbc.sqlserver.SQLServerDriver” this is incorrect.

com.microsoft.jdbc.sqlserver.SQLServerDriver # incorrect class name
com.microsoft.sqlserver.jdbc.SQLServerDriver # correct class name

My Machine Setup:

  • Windows 7  Enterprise – 64 Bit
  • R Studio Version 0.97.551
  • R version 3.0.1 (2013-05-16), platform x86_64-w64-mingw32
  • Microsoft SQL Server 2008, 2012 Installed

If you use a tool like 7-zip to explode the jar file you will notice the class files are located at:

“C:\jdbc\sqljdbc_4.0\enu\sqljdbc4\com\microsoft\sqlserver\jdbc\SQLServerDriver.class”


# reference document on RJDBC
# http://cran.r-project.org/web/packages/RJDBC/RJDBC.pdf
# install.packages("RJDBC",dep=TRUE)
library(RJDBC)
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver" , "C:/jdbc/sqljdbc_4.0/enu/sqljdbc4.jar" ,identifier.quote="`")
conn <- dbConnect(drv, "jdbc:sqlserver://SERVERNAME:55158;databaseName=master", "sa", "password")
d <- dbGetQuery(conn, "select * from sys.databases where database_id <= 4 ")
summary(d)

You can download this script from here

I also tested using the Microsoft Driver and connecting to the same SQL Server using Ubuntu.

I have tested the connecting to sql server using R from Windows, Ubuntu and OS X. Below are links to the gists which contain the code.

Windows

Ubuntu

Mac OS X 

For the code used in that example look at my gist

Advertisements

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