Connect R to SQL Server 2016


There are various ways to connect to SQL Server from R using different techniques – one of them is by using ODBC. Here I will show you same.

I assume you have already install SQL server and R studio, if not please refer
  1. How to install Sql server 2014 (click here)
  2. How to install R (click here)


When you connect first time to a database, you need to perform some one-time tasks also, which are:


  • Create a ODBC DSN data source
  • Install necessary R-packages from CRAN

Create a ODBC DSN data source


Step 1: Open "Administrative Tools" and "ODBC Data Sources (32 bit)"



Step 2: Make sure you use "Run as administrator"



Step 3: On the tab "User DSN" press "Add"and Select "SQL server"




Step 4: Add your database name, Description and SQL Server instance name


If you are not sure of sql server name, Go to SSMS and run query (Select @@servername)




Step 5: Click next and add SQL server id /password for SQL authentication


Step 6: Add default database to your preferred database, I am using "Dummy"


Step 7: Make sure you test data source



Install necessary R-packages from CRAN

STEP 1: Once this steps completed successfully, Open R Studio and run command  install.packages("RODBC") to install RODBC package from internet
Please type commands in studio, don't copy paste



Step 2: Now to use RODBC package use command library ("RODBC")


Now we will see commands to Connect database with R

It's a 3 steps process
1.       When calling the database you first have to open a connection to the database using command odbcConnect().
2.       Perform you operations
3.       close the connection again using command odbcClose().

1.       Open connection, here SQLSERVERVARIABLE is connection name, you can use any name
2.      Use sqlfetch to pull data from sql server

3.      Now once operation is done , we will close the connection



2 comments:


  1. Thanks for posting such an interesting inforamtion, Please keep updates us and make us up to date....

    ReplyDelete

  2. Hi, It's very best advantageous blogs.I read this subjects blog such a great blog and good sharing I'll be like this informative post.Thank you for selecting the time to provide us with your valuable knowledge.C#.NET Online Course

    ReplyDelete