SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL R Library RDOBC Script to Connect to SQL Server

One of the methods for R developers on SQL Server to connect to SQL Server databases in order to execute SQL commands and query data is using RODBC R library. RODBC package simply provides an ODBC database connectivity from R scripts to data developers.

Please refer to RODBC package for official documentation and more details about the R library and provided R functions.

In this guide, I want to share an interesting case I experienced during my developments on my new machine configuration.

SQL Server and In-Database Machine Learning Services with R

I have R Studio installed and I also installed RODBC library on R Studio.
Then I executed following script step by step on R Studio to connect to a SQL Server instance and query a database table by executing a SQL Select statement as follows.

Please note that I'm using a named instance for target SQL Server

library(RODBC)
sqlConn <- odbcDriverConnect('driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
fix(ds)

As R Studio developers can see I could connect to SQL Server instance using SQL login name and password using RODBC R package odbcDriverConnect function.
Then by executing sqlQuery function I can pass a SQL statement to run on connected SQL Server database

connect and query SQL Server database from R-Studio using RODBC R library

When I tried to execute the same R script on SQL Server 2019 instance where R is installed as a component of the current SQL Server 2019 instance, I got errors.

If you have not installed R (In-Database Machine Learning Services) on your SQL Server please check SQL Server 2019 installation steps.
To enable database programmers to build and run R Scripts on SQL Server, database administrators have to enable execution of external scripts on that instance.
Please follow instructions given at Enable sp_execute_external_script to Run Python or R Script on SQL Server to complete this step.
After all these steps are completed, you can also execute R scripts on your SQL Server database.

Here is the SQL script I created to execute external R script to connect to an other SQL Server instance. (I exeute R script on SQL Server 2019 database instance to connect to a database which is created on SQL Server 2016 instance). This script is created for test purposes. So instead of creating a linked server, I use RODBC library for remote connection.

EXEC sp_execute_external_script
@language = N'R',
@script = N'
Sys.setenv(LANG = "en");
library(RODBC)
sqlConn <- odbcDriverConnect(''driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test'')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
OutputDataSet <- ds
';

Please note that in above ODBC connection settings for named SQL Server instance I used double "\". The first one is used as escape character. So instead of "kodyaz\sql2016" I used "kodyaz\\sql2016"

Maybe it is worth to mention. uid is for SQL login name and pwd is for the password of the SQL login. Here I created "test" user with password as "test" for test purposes :)

execute R script on SQL Server using RODBC R package

Error message displayed on SQL Server Management Studio is:

Msg 39004, Level 16, State 20, Line 374
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 374
An external script error occurred:
Error in sqlQuery(sqlConn, "SELECT * FROM dbo.Country;") :
first argument is not an open RODBC channel
Calls: source -> withVisible -> eval -> eval -> sqlQuery
In addition: Warning messages:
1: In odbcDriverConnect("driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test") :
[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server yok veya eriĆ¾im engellendi.
2: In odbcDriverConnect("driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test") :
[RODBC] ERROR: state 01000, code 10061, message [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()).
3: In odbcDriverConnect("driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test") :
ODBC connection failed

Error in execution. Check the output for more information.
Error in eval(ei, envir) :

Msg 39019, Level 16, State 2, Line 374
An external script error occurred:
Error in execution. Check the output for more information.
Calls: runScriptFile -> source -> withVisible -> eval -> eval -> .Call
Execution halted

I could connect to the target SQL Server named instance using SQL Server Management Studio SSMS or using R-Studio even with the same R script.

Then I decided to check the port assigned for the SQL Server instance.
So I launched SQL Server Configuration Manager
While checking configuration data on SQL Server Configuration Manager tool, I realized I did not enable TCP/IP protocol for the target server.

I was trying to connect SQL2016 SQL Server instance from SQL2019CTP21 database instance. So I checked protocols for SQL2016 named instance.

protocols enabled in SQL Server Configuration Manager tool

As seen in above screenshot from Configuration Manager tool, TCP/IP protocol for SQL2016 SQL Server instance is disabled (by default). So I enable this protocol (TCP/IP) by right click on the protocol name and choose Enable option.

enable TCP/IP protocol for SQL Server

To make the changes take affect the related SQL Server service should be restarted.
Again using the same tool, SQL Server Configuration Manager you can restart the service. Please note that user connected to the database instance will not be able to reach the server at that time. That will be a downtime that will probably take less than a minute.

As seen in following screenshot, drill-down SQL Server Services node find the target database instance. Open the context menu by right clicking on the SQL Server name and choose Restart

restart SQL Server instance

Now R developers or data science developers using SQL Server R Services can execute the R script once more.

execute R script including RODBC library to connect to SQL Server

EXEC sp_execute_external_script
@language = N'R',
@script = N'
Sys.setenv(LANG = "en");
library(RODBC)
sqlConn <- odbcDriverConnect(''driver={SQL Server};
 server=kodyaz\\SQL2016;
 database=testdata;
 uid=test;pwd =test'')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
OutputDataSet <- ds
';
GO

When the TCP/IP protocol is enabled or activated for the server, it is also possible to use "servername\instancename,port" or "servername,port" form for target server identifier.
It is possible to determine the port number using TCP/IP protocol Properties screen IP Addresses tab.
In IPAll section, if you see a port number other than 0 for TCP Dynamic Ports, you can use it in above forms, too.

If the SQL login does not exist or failed with authentication, the error message is different and indicating the login problem as "Login failed for user"
For example,

[RODBC] ERROR: state 28000, code 18456, message [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test'.

If you want to use trusted_connection and Windows user to authenticate on the target SQL Server instance, then first you have to add current SQL Server instance R user group (where you execute the R script) as a Windows login on target SQL Server instance.

So I added "KODYAZ\SQLRUserGroupSQL2019CTP21" as login user for the server and for the database on target SQL Server instance "KODYAZ\SQL2016"

SQLRUserGroup of one SQL Server instance as login on other database instance

You can simply define the SQL Server login properties as follows for the target database (testdata db). Please note that I marked "db owner" to query database objects without dealing with additional security settings at object level.

SQL Server login properties of SQLRUserGroup

Mapping the SQL Server login to testdata database will create a database user on testdata automatically.

The second step will be modifying the R-Script RODBC connection string.
It is enough to replace "uid=test;pwd=test" with "trusted_connection=yes" as seen below.

EXEC sp_execute_external_script
@language = N'R',
@script = N'
Sys.setenv(LANG = "en");
library(RODBC)
sqlConn <- odbcDriverConnect(''driver={SQL Server};
server=kodyaz\\SQL2016;
database=testdata;trusted_connection =yes'')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
OutputDataSet <- ds
';

SQL Server and In-Database Machine Learning Services with R







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems