Home > R > Connect to SQL Server in R without ODBC DNS connection

Connect to SQL Server in R without ODBC DNS connection


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

library(RODBC)

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

mydata

Step 6: Close the connection

close(MSConnection)

RODBC documentation

Advertisements
Categories: R Tags: ,
  1. February 9, 2016 at 11:07 pm

    How do i use variable names for server and database?

    • February 10, 2016 at 11:51 am

      You can construct the connection string with database and other property values before passing the connection string to the function.

  2. February 9, 2016 at 11:09 pm

    How do I use variable names for server and database in this?

  1. No trackbacks yet.

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

%d bloggers like this: