Creating Connectivity Between R Studio & SQL Server 17


Intro:

For Data Science, SQL Server 17 has optional built in R functionality. It also has the ability to call R Studio from within SQL Server. The prerequisite is reliable connectivity between SQL Server and R Studio.

Here are the steps to configure SQL Server and R to work with each other.

——————————————————-

Initial Technology Stack To Install:

On your licensed Windows 10 machine, install:

SQL Server 17
R Studio, 1.1.463
R, 3.5.1

——————————————————-

R Studio Drivers and Packages:

After installing R Studio, there are a number of packages that also need to be installed. The packages facilitate the connectivity.

The packages are:
DBI
odbc
RODBC

 

Some of these packages also need to be installed, or updated. Install all of them.

installr
RTools
devtools
xopen
rstudioapi
pkgconfig
utils
ini

 

Install Packages over the Internet Using the GUI:

You can install the packages via the GUI in R Studio.  See the picture.

R_Package_GUI_Install

 

Install Packages over the Internet Using the Command Line:
> install.packages(“odbc”)
Installing package into ‘C:/Users/r/Documents/R/win-library/3.3’
(as ‘lib’ is unspecified)
trying URL ‘https://mran.microsoft.com/snapshot/2017-05-01/bin/windows/contrib/3.3/odbc_1.0.1.zip’
Content type ‘application/zip’ length 855340 bytes (835 KB)
downloaded 835 KB

package ‘odbc’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\r\AppData\Local\Temp\RtmpGku7vs\downloaded_packages
I was told to use at least version: 1.1. But after the update, the version was only 1.0.1. Hmm.

————

So, the default online repository may not update to the latest software.
In the R Studio Console, check the repository with:

 

getOption(“repos”)

Mine responded:
CRAN
https://mran.microsoft.com/snapshot/2017-05-01”
CRANextra
http://www.stats.ox.ac.uk/pub/RWin”

 

With Manual installs, set the parameter, repos:

install.packages(“DBI”, repos = “http://cran.us.r-project.org”)

install.packages(“odbc”, repos = “http://cran.us.r-project.org”)

etc

Accept everything that R Studio suggests that you also install. Prerequisites. Other packages. Etc.

R_Package_Manual_Install

——————————————————-

 

Windows Drivers:

In addition to the drivers on R Studio, you also need drivers on the Windows 10 OS.  Windows always demands drivers.

To check the drivers that are installed on Windows 10:

Go to
Control Panel -> Adminstrative Tools -> ODBC Data Sources (32-bit)
Control Panel -> Adminstrative Tools -> ODBC Data Sources (64-bit)

 

Originally, my laptop had only:

 

32 Bit:

ODBC Driver 13 for SQL Server 2017.140.2002.14
SQL Server 10.00.17763.01
SQL Server Native Client 11.0 2011.110.6540.00

64 Bit:

ODBC Driver 13 for SQL Server 2017.140.2002.14
SQL Server 10.00.17763.01
SQL Server Native Client 11.0 2011.110.6540.00
SQL Server RDA Native Client 11.0 2011.110.5069.66

Download and install the ODBC drivers (see link below)

msodbcsql_17.2.0.1_x64.msi
msodbcsql_17.2.0.1_x86.msi

After the installation, I had all these drivers:

32 bit:

Drivers_32_odbc

64 bit:

Drivers_64_odbc

Reboot your laptop.  Windows likes frequent rebooting after installations.

——————————————————-

 

SQL Server:

SQL Server needs to be configured so that a user (R Studio) can login into a database with a username and password.

—-

Change SQL Server Security Settings:

Open SQL Server Management Studio (SSMS)

Right click the Server (immediately above “Databases”)
Properties
Security

Check: SQL Server and Windows Authentication mode
Ok

SSVR_Security

Restart the SQL Server database, by:

From SSMS, right click the database
Restart

Or, just reboot your laptop.

Create a SQL Server User:

In SSMS,
Right click Security
New
Login
Enter the username
Password
Uncheck Enforce Password Policy

NewSSVRUser

Set the properties of the user:

SSMS
Security
Logins
Pick the user
Properties
Server Roles

Check Public and SysAdmin

Ok

UserServerRoles

Test the Login:

SSMS
Object Explorer
Right click the database
Connect

Use SQL Server Authentication
username
password

ConnectUser

——————————————————-

Configuring R Studio:

After updating drivers on both R Studio, and Windows 10, in R Studio, I went to:
Connection, New Connection
and noticed that there were a lot more options. See:

R_Create_Connection

Previously, there was only two options:
ODBC Driver 13 for SQL Server
Spark

Now, there were six options

 

In R Studio, Setting Default R Installation:
RStudio Menu
Tools
Global options
General

RStudio_R_installation

 

Creating the Connection From R Studio to SQL Server:
In R Studio:

library(odbc)
library(rstudioapi)
options(database_userid = “ruser1”)

connToSQLSrvr <- DBI::dbConnect(odbc::odbc(),
Driver = “ODBC Driver 17 for SQL Server”,
Server = “Acer8481Windows”,
Database = “Flightdata1”,
UID = getOption(“database_userid”),
PWD = rstudioapi::askForPassword(“Enter Database Password”),
Port = 1433)

WordPress always overrides anybody’s formatting, so you can’t see the indents.

connToSQLSrvr

Modify the variables accordingly.  Be very careful with syntax.

In R Studio, these lines of code will create an object, connToSQLSrvr, which will allow you to connect to SQL Server.

Note that the odbc driver, needs the exact spelling to work.

——————————————————-
TroubleShooting Connections:

Check for:

Missing Commas (syntax)

InCorrect parameters (syntax)

InCorrect User String (syntax)

Wrong ODBC driver

Wrong SQL Server name

Wrong Database

Using the wrong SQL Server name:
Server=”Acer8481Windows10″,
rather than:
Server=”Acer8481Windows”,

This caused my RStudio to hang, and I had to reboot the laptop to regain control of R Studio.

 

——————————————————-

Useful Links:

Download Microsoft ODBC Driver 17 for SQL Server – Windows, Linux, & macOS:
https://www.microsoft.com/en-us/download/details.aspx?id=56567
To download ODBC drivers:
msodbcsql_17.2.0.1_x64.msi
msodbcsql_17.2.0.1_x86.msi

 

R ODBC nanodbc error when not using DSN
https://stackoverflow.com/questions/50139986/r-odbc-nanodbc-error-when-not-using-dsn

Perform customer clustering using R and SQL Server ML Services
https://microsoft.github.io/sql-ml-tutorials/R/customerclustering/step/2.html

The tutorial.

——————————————————-

Special Thanks To:
Lateef
Alex
Bruno

For discovering the various secrets required to get this technology stack to work.
Windows, R Studio, SQL Server, ODBC, etc.

——————————————————-

Some Errors I Had:

No particular order. Just in case someone searches for the error. The solution will be in correctly installing and configuring all the different parts.

1: In value[[3L]](cond) :
namespace ‘DBI’ 0.6-1 is being loaded, but >= 1.0.0 is required

Error in odbc_connect(connection_string) :
nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Error: nanodbc/nanodbc.cpp:950: 08001: [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [5].

Error: unexpected symbol in:

Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
namespace ‘DBI’ 0.6-1 is being loaded, but >= 1.0.0 is required
Error in loadNamespace(j <- i[[1L]], c(lib.loc, .libPaths()), versionCheck = vI[[j]]) :
namespace ‘Rcpp’ 0.12.10 is being loaded, but >= 0.12.11 is required

 

 

 

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: