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
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
- Create ODBC Data Source
- Setup R environment to connect to SQL Server
- 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:
Load the RODBC library to current environment to use:
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
Thanks for reading this post.