As you may already know In-database Analytics (also known as Advanced Analytics) is available in SQL Server 2016. To simplify, “In-database Advanced Analytics”: you can run powerful statistical / predictive modelling (from R) inside SQL Server. Read the official definition here.

Check Out SQL Latest Bits

If you’re new to R and SQL Server R Service, my recommendation is to go through the Getting Started.docx document that comes in SQLServer2016CTP3Samples.zip (unzipped path “SQLServer2016CTP3Samples\Advanced Analytics\Getting Started.docx”) from SQL Server 2016 CTP 3 Sample. Please note that it is possible to have more than one R Servers running on the same machine – e.g one from Microsoft R Open and another one from SQL Server R Services.

Watch Out for Missing Package Issue

When you start playing with R in SQL Server, sooner or later you would need to install some packages, for example ggplot2. You may run into a problem that sounds like this “Error in library(“ggplot2”) : there is no package called ‘ggplot2’“.

The following script is used in the iris_demo.sql (SQLServer2016CTP3Samples\Advanced Analytics\iris_demo.sql), and would cause a missing library error if you don’t have the packages installed on SQL Server R Services yet.

USE [sqlr]
GO
EXECUTE [dbo].[get_iris_plot1]

The error indicating a package has not been installed in SQL Server R Services looks similar to the following.

Msg 39004, Level 16, State 20, Line 33
 A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
 Msg 39019, Level 16, State 1, Line 33
 An external script error occurred:
 Error in library("ggplot2") : there is no package called 'ggplot2'
 Calls: source -> withVisible -> eval -> eval -> library

Error in ScaleR.  Check the output for more information.
Error in eval(expr, envir, enclos) :
Error in ScaleR.  Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
Msg 11536, Level 16, State 1, Procedure get_iris_plot1, Line 5 [Batch Start Line 33]
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Installing R Packages

The Getting Started document says that you will need to run installpackages.r. You can open this on Visual Studio on Run As Administrator mode (after installing R Tools for Visual Studio) and run it.

The trick is to ensure that the R Tools is running on the SQL Server R Services.

  1. To do this, go to Visual Studio > R Tools > Options.
    Accessing R Options

  2. A dialog box similar to the following will open. Navigate to R Tools > Advanced > R Engine. The default location of SQL Server R Services is here: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES. Please update it with the relevant path to your R_SERVICES directory.
    R Engine Options
  3. After correcting the R Engine path to SQL Server R Services, click OK.
  4. You can now re run installpackages.r. Place the cursor on the line you want to run and press Ctrl + Enter.Run instlalpackages.r in R

Then you can go back to SSMS to run that EXECUTE [dbo].[get_iris_plot1]. It will return a VARBINARY data representing the image.

Execute R script in SQL Server

Or, use Report Builder and open up the iris_plots.rdl from the sample folder where you can render R plots just like below.

Iris Plot in Report Builder

Now if you have any other packages that you want to use in your R script inside SQL Server, you can install them on SQL Server R Services first using R Tools in Visual Studio.

The Rogue Way

If you have more than one R server, you can also install the packages you need in one server and copy them across to the other R server directories. This is obviously not the official way … But a trick that I learned from my good friend, Tomaž Kaštrun (b | t) – an R (and SQL Server) expert.

Below is a screenshot of where the libraries on Microsoft R Open (MRO) sit with all packages installed.A sample directory of R Server's library

R and Visualization

Now that you know how to set it up, join Tomaž Kaštrun and I in 24 Hours of PASS Evolution of the Data Platform next week for 5 Techniques to Beautiful Data Insights with R and SSRS on May 25, 2016 at 4PM GMT (9AM PDT). Don’t forget to register for it and other interesting sessions!

I'm speaking at 24 HOP Evolution of the Data Platform, May 25-26

Wrap Up

Use Adventure Works SQL Server 2016 CTP 3 to try out different Hero features in SQL Server 2016. Check out R to manipulate data or to generate plots. Don’t forget to install the right packages first on the SQL Server R Services.

Further Reading

SQL Server 2016 R Services: Guide for Server Configuration by Koen Verbeeck

Introducing Microsoft R Server by Henk Vandervalk

3 Responses

Leave a Reply

Your email address will not be published. Required fields are marked *

Some simple Math is good for your brain! Thanks, Ms SQL Girl. * Time limit is exhausted. Please reload the CAPTCHA.

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