SQL Server R Services

The odbcConnect function in R Programming is handy to establish a connection between SQL Server and R. This odbcConnect function starts the SQL Server R Services. And the sqlQuery function extracts data from SQL tables and store the data in a Data Frame. Let me show how to set up SQL Server R Services using ODBC Connection and SQL Server Native Client connection provided by the RODBC library with example.

Syntax of an odbcConnect and SqlQuery in R

The syntax to start the SQL Server R Services is

odbcConnect(ConnectionName, uid = "User name", pwd = "password")
  • Connection Name: Here, specify the ODBC Connection, or SQL Server Native Client connection name.
  • uid: Please specify the username who has access to SQL Server.
  • pwd: Please specify the password for the above username

and the syntax behind the R Programming data extraction is

sqlQuery(ConnectionString, query, errors = TRUE,..., rows_at_time)

There are many arguments supported by the sqlQuery function in R programming, and the following are some of the most used arguments in real-time:

  • ConnectionString: Here, specify the Connection String details.
  • query: Please specify any valid SQL statement.
  • errors: It accepts a Boolean value. If it is TRUE, and when the error encountered, then it returns -1, or error message.
  • rows_at_time: You can restrict the number of rows to fetch at a time, and the value can be between 1 and 1024.

Before we get into the SQL Server R Services example, let me show you the data that we are going to extract.

Setting Up SQL Server R Services 1

From the above screenshot, see that we are selecting the top 50 rows from the DimCustomers table. The SQL SELECT Statement that we used in this example is:

NOTE: RODBC library is not a built-in library. So, you have to install that library from the CRAN mirror. Please refer Install R Packages article for the package installation in R Programming.

SELECT TOP 50 [CustomerKey]
      ,[FirstName]
      ,[LastName]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[EmailAddress]
      ,[YearlyIncome]
      ,[EnglishEducation]
      ,[EnglishOccupation]
  FROM [AdventureWorksDW2014].[dbo].[DimCustomer]

Setting Up SQL Server R Services ODBC Connection

In this example, we show how to establish the connection between SQL Server and the R language using the ODBC Connection with a practical example.

# Getting Started with SQL Server R Services
# Importing RDBOC Library
library(RODBC)

# Setting up connection to SQL Server Database
conn <- odbcConnect("SQL Server", uid = "tutorialgateway", pwd = "password")

# SQL Query we are going to use
data <- "SELECT TOP 50 [CustomerKey], [FirstName], [LastName], [BirthDate], [MaritalStatus], [Gender]
        ,[EmailAddress],[YearlyIncome], [EnglishEducation], [EnglishOccupation]
      FROM [AdventureWorksDW2014].[dbo].[DimCustomer]"

# Import Data From SQL Server
query.Result <- sqlQuery(conn, data)
odbcClose(conn)
dim(query.Result)
query.Result
Setting Up SQL Server R Services 2

First, we are importing the RODBC library, and it has all the required functions to set up a connection with SQL Server from R Studio.

library(RODBC)

Next, we are using the odbcConnect function to start the SQL Server R Services. Here, “SQL Server” is the ODBC Connection that we created in our previous post. Please replace the connection name, uid, pwd values with your credentials.

conn <- odbcConnect("SQL Server", uid = "tutorialgateway", pwd = "password")

Next, we are importing data from SQL Server

query.Result <- sqlQuery(conn, data)

The following statement closes the established connection.

odbcClose(conn)

The dim function returns the number of rows and columns returned by the Data Frame query.result.

dim(query.Result)

Lastly, we are printing the output

query.Result

Setting Up SQL Server R Services Native Client Connection

In this example, we show how to set up the connection between SQL Server and R Programming using the SQL Server Native Client connection with a practical example. Here, “SQLServer” is the connection name that we created in our previous post.

# Use SQL Server Native CLimnt Connection to Set up SQL Server R Services

# Importing RDBOC Library
library(RODBC)

# Setting up connection to SQL Server Database
conn <- odbcConnect("SQLServer", uid = "tutorialgateway", pwd = "password")

# SQL Query we are going to use
data <- "SELECT TOP 10 [FirstName], [LastName], [BirthDate], [MaritalStatus], [Gender]
        ,[YearlyIncome], [EnglishEducation], [EnglishOccupation]
      FROM [AdventureWorksDW2014].[dbo].[DimCustomer]"

# Import Data From SQL Server
query.Result <- sqlQuery(conn, data)
odbcClose(conn)
dim(query.Result)
query.Result
Setting Up SQL Server R Services 3