Tuesday, September 6, 2016

Enabling R Service and Verifying Local R script execution

There are several steps  involved in setting up R Services work with SQL Server 2016() and one of which is enabling R Service and Verifying Local R script execution.

In my previous post (http://hodentekmsss.blogspot.com/2016/07/problem-enabling-r-service-in-sql.html) I have completely described the installation of SQL Server 2016 Developer's edition during which the R Server was also installed as shown here:

In order to run R in SQL Server 2016 you need to use the sp_execute_external_scripts stored procedure. In order to do use this, you need first set the external scripts enabled option, which by default is OFF.
The suggested way is to configure by running the following statement:
sp_configure 'external scripts enabled', 1; 
When run on the named instance on which R Server is also configured (Hodentek8\Ohana) we see the following result:

Let us see if  running the sp_execute_external_script written in R works:

exec sp_execute_external_script @language=N'R',
@script=N'OutputDataSet <-InputDataset',
@input_data_1=N'select 1 as hello'
with result sets (([hello] int not null));

This returns the following error:

Msg 39011, Level 16, State 1, Line 4
SQL Server was unable to communicate with the LaunchPad service.
Please verify the configuration of the service.

You can open up Services window from Control Panel and start the service. This is the Launch Pad services to work with Analytics.

After this run the R script again as shown.

Booyah! It works! This was the response expected and the stored procedure works.