Invoking R script file in SQL Server 2016

The sp_execute_external_script is a stored procedure to execute script at an external location. The system sp_execute_external_script stored procedure was introduced in SQL Server 2016 and only supports R language.

This post, I will show how to invoke an R script file in SQL Server 2016.

Prerequisites

1. Install SQL Server 2016 with R Server
2. Enable External Script

If the external script is not enabled in your SQL Server Instance, you will get the error below:

'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.

To enable it, run the script below:

sp_configure 'external scripts enabled', 1;

RECONFIGURE;

3. Restart SQL Server service for the instance where External Script was enabled

4. Give EXECUTE ANY EXTERNAL SCRIPT database permission for the user that needs to execute sp_execute_external_script

Invoking R script file

To run external R file you need to use source function, as below:

EXECUTE sp_execute_external_script
@language = N'R'
,@script = N'source("C:\\Projects\\R\\Scoring.r");'
,@input_data_1 = N''
,@output_data_1_name = N'score_data'
WITH RESULT SETS ((
"Result.Score" float not null,
"Product" varchar(100)));
GO

If there is missing library required by your R code you can install it as below:

EXECUTE sp_execute_external_script
@language = N'R'
,@script = N'if(!is.element("data.table", installed.packages())) {install.packages("data.table", dependencies = T) } else {library("data.table")}
source("C:\\Projects\\R\\Scoring.r");'
,@input_data_1 = N''
,@output_data_1_name = N'score_data'
WITH RESULT SETS ((
"Result.Score" float not null,
"Product" varchar(100)));
GO

Note: @script is a nvarchar(max).

In SQL Server 2016 you can use sp_execute_external_script to run R code in SQL Server and also invoke R script file.