Skip to content

SQL Named Instances in R with SQL 2016

The amount of information and training for SQL 2016 is deep and rich. I am in the process of creating training materials and was reviewing the R tutorials available on MSDN. There is a detailed user-friendly tutorial on using R in SQL 2016 titled “Data Science End-to-End Walkthrough” available here;

SQL Server R Services Tutorials

The tutorials walk you through various data science scenarios that show what is possible in SQL Server R Services. You can build custom R solutions, deploy a model to SQL Server, and then score the model using transactional or reporting data available on the server or provided through other internal or external data sources.

Named Instances and R Services

One thing that I discovered in the tutorials was the assumption that your databases are being hosted in the default SQL instance. I usually set up my instances as named instances. In lesson 2, “View and Summarize Data using R” the script “RSQL_RWalkthrough.R” has the following connection string:

connStr <- “Driver=SQL Server;Server=RSQLSVR01;Database=NYCTaxi_Sample;Uid=;Pwd=”

This example is not a named instance which would be in the form, “server\instanceName”.

The connection string for a named instance requires a double slash in between the server and instance name. The following change to the script fixed the error I was having when using the instance name “SEYSQL”:

connStr <- “Driver=SQL Server; Server=RSQLSVR01\\SEYSQL; Database=NYCTaxi_Sample;Uid=”xxxx”;Pwd=”xxxx”

The interesting error was that the slash in the server name was being taken as a control code. Using double slashes solved this issue. The same technique is used when using file locations in R such as:

sqlShareDir <- paste(“C:\\AllShare\\”, Sys.getenv(“USERNAME”), sep=””)

Key Benefit of using R in SQL 2016

The benefit of having R integrated with your TSQL code is that you can code in R and place it in stored procedures allowing the data and results to be consumed in the other products in the BI Stack. I have taken the same code and run them through both RStudio and R in SQL 2016. It will be interesting to see how this can be leveraged in real-world analysis scenarios.

Give the latest CTP preview a try, available here;

Steve

No comments yet

Leave a Reply

You may use basic HTML in your comments. Your email address will not be published.

Subscribe to this comment feed via RSS

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: