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