In this blog post I will describe how I connected RStudio running on an Ubuntu Linux Notebook through JDBC to a Microsoft SQL Express R2 2008 Server. An overview can be seen here:
What I used:
- Ubuntu Linux LTS 14.04 (Trusty)
- Windows Vista
- Microsoft SQL Server Express 2008 R2
- RStudio Desktop
- R 3.2.3 from the Uni Münster Mirror
- Microsoft JDBC Driver 3.0 for SQL Server
- RJDBC
Installing Microsoft SQL Server
So after downloading Microsoft SQL Server Express you have to go through the set up wizard. Afterwards you can start it.
Then you have to make sure that a remote connection is possible. You have to do the following:
- Enable remote connections on Microsoft SQL Server
- Enable TCP/IP connections in SQL Server Configuration Manager
- Set Port to 1433 in IPAll configuration and delete everything from the dynamic Port
- Open the Windows Firewall for Port 1433
You can check if this worked by using netstat in a cmd console.
Install R
The version of R shipped with Ubuntu 14.04 wasn’t recent enough (3.0.2) I had problems to install a recent plyr version (newer then 1.8.1). So I used the following tutorial. How To Set Up R on Ubuntu 14.04 to set up R 3.2.3.
Install RJDBC
It should be as easy as:
install.packages("RJDBC")
I had problems getting rJava running with Java 8 because libjvm.so wasn’t found. I did the following in a terminal:
export LD_LIBRARY_PATH="/usr/lib/jvm/java-8-oracle/jre/lib/amd64/server:/usr/lib/x86_64-linux-gnu/"
sudo -E updatedb
sudo R CMD javareconf
You should start RStudio from the same Terminal as well. Afterwards it worked.
Install RStudio
To install RStudio just download the deb package and install it.
Install JDBC Driver
This is straight forward. Just download it and unpack it.
tar xzf ~/Downloads/sqljdbc_3.0.1301.101_enu.tar.gz
Afterwards you will find the driver here:
/etc/sqljdbc_3.0/enu/sqljdbc4.jar
The big moment
Now you are read to connect to your server:
options( java.parameters = "-Xmx2g" )
require(RJDBC)
# specify the driver
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
"/etc/sqljdbc_3.0/enu/sqljdbc4.jar")
conn <- dbConnect(drv, "jdbc:sqlserver://192.168.178.1", "user", "password")
# then build a query and run it
version <- dbGetQuery(conn, "SELECT @@version")
version
# Should output: 1 Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86) \n\tJun 28 2012 08:42:37 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)\n
Leave a Reply