Import data from Microsoft SQL server to R using RJDBC package

Environment

• Windows 7, 64-bit
• R 2.15.1 (32- & 64-bit)
• Microsoft SQL Server 2008

Installation & Usage

1. Download JDBC driver from Microsoft ( Link ) and install it so that RJDBC plugin is able to interact with SQL Server database via the Java interface.
• You can install the driver to a local folder, such as C:\Program Files\Microsoft SQL Server JDBC Driver 3.0
2. The latest RJDBC release is available from CRAN, install it using following command:

   install.packages("RJDBC",dep=TRUE)

3. Usage of JDBC

   drv <-JDBC (driverClass, classPath)
   conn <- dbConnect(drv, "jdbc:sqlserver://serverName", "userID", "password")

• driverClass --- name of the Java class of the JDBC driver to load. If empty, it is assumed that corresponding JDBC drivers were loaded by other means.
  For Microsoft SQL Server, driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
• classPath --- class path that needs to be appended in order to load the desired JDBC driver. Usually it is the path to the JAR file containing the driver. For example, "C:/Program Files/Microsoft SQL Server JDBC Driver 3.0/sqljdbc_3.0/enu/sqljdbc4.jar"
• When specifying database server name, I tried following two ways, and looks like both worked
  • jdbc:sqlserver://xxx.xx.xxx.xx
  • jdbc:sqlserver://xxx.xx.xxx.xx:port;database=databaseName
4. Example code:

   library(RJDBC)
   drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "C:/Program Files/Microsoft SQL Server JDBC Driver 3.0/sqljdbc_3.0/enu/sqljdbc4.jar")
   conn <- dbConnect(drv, "jdbc:sqlserver://xxx.xx.xxx.xx:1433;database=My_MARKET_DATA", "admin", "123456789")
   #dbListTables(conn)
   sqlText <- paste("SELECT * FROM myTable", sep="")
   queryResults <- dbGetQuery(conn, sqlText)