SQL Server R Services

The odbcConnect function in this 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 tables and stores the data in a Data Frame.

Let me show how to set up SQL Server R Services using ODBC and Native Client connection provided by the RODBC library with an example.

SQL Server R Services Syntax

The syntax to start the SQL Server R Services dbcConnect and SqlQuery is shown below.

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

And the syntax behind the R Programming sqlQuery data extraction is

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

The sqlQuery function in R programming supports many arguments, 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 statement.
  • errors: It accepts a Boolean value. If it is TRUE, and when the error is encountered, then it returns -1, or an 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 we will extract.

Customer Table 1

From the above screenshot, see that we are selecting the top 50 rows from the DimCustomers table. The 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 Packages article for the package installation in 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 with a practical example.

# Importing RDBOC Library
library(RODBC)

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

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

This example shows how to set up the connection between SQL Server and R Programming using the Native Client connection with a practical example. Here, the first argument is the connection name that we created in our previous post.

# Importing RDBOC Library
library(RODBC)

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

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

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