Archive for the ‘R’ Category

Connect to SQL Server in R without ODBC DNS connection

September 25, 2015 3 comments

In the previous post, I have explained the steps to connect to SQL Server with ODBC DSN.

In this blog post, I am going to connect to SQL Server without ODBC DSN. But, will be using driver name instead.

Step 1: RODBC must be installed in R. Please read (Connect to SQL Server in R for detail)

Step 2: Load the RODBC library


Step 3:Connecting to SQL Server with Driver name

MSConnection <- odbcDriverConnect(“DRIVER=SQL SERVER;SERVER=.;Trusted_Connection=Yes”)

This connection point to the default database initially. But, we can run USE statement to change the database.

sqlQuery(MSConnection, “USE test”)

Step 4: Read the table data to a variable

mydata <- sqlQuery(MSConnection, “Select * from dates”)

Step 5: Display data by just typing the variable name in R command environment


Step 6: Close the connection


RODBC documentation

Categories: R Tags: ,

Connect to SQL SERVER in R

September 20, 2015 1 comment

R is an OPEN SOURCE software environment for statistical computing and graphics. It is easy to use and create valuable data visualization in a minute. Learn more about R.

This blog post is to explain the steps to connect to SQL Server database in R and display simple graphics.

Over all steps

  1. Create ODBC Data Source
  2. Setup R environment to connect to SQL Server
  3. Query the SQL table, display data and graphics

Step 1: Create ODBC Data Source:

Goto, Control Panel -> Administrative Tools -> Data Sources (ODBC)


Click Add button


select SQL Server Native Client and click Finish


Give data source name and SQL Server name to connect and Click Next


Select an appropriate SQL Server authentication option and give valid credential. In this example, we are using Integrated Windows Authentication. Click next


Select the database to work with and modify the application intent option to Read-only. These settings are for this exercise only. We may use default setting and click next.


Click finish and TEST connection


Step 2: Setup R environment to connect to SQL Server.  Please install R software.

Install RODBC package to R:

  • install.packages(“RODBC”)

Load the RODBC library to current environment to use:

  • library(RODBC)

Connect to SQL Server using ODBC connection:

  • wrpt <-odbcConnect(“weatherdata”)

We have just connected to SQL Server and the R connection variable name is wrpt. We can use this variable to query a table and more.

Step 3: Query the SQL table to display data and graphics

This statement loads the entire table “tblTemp” in to the variable weather.

weather <- sqlFetch(wrpt, “tblTemp”)  

This statement displays the entire table


This statement displays the summary of the data-set



This statement displays a graph with daily max temperature for a  month

plot(weather$maxtemp, type=”l”)


R manuals

Thanks for reading this post.

Categories: R Tags:
%d bloggers like this: