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
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