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;
  1. Restart SQL Server service for the instance where External Script was enabled
  1. 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.

1 thought on “Invoking R script file in SQL Server 2016

  1. I am not able to install packages from within the R script. Whatever process is trying to write to the install folder doesn’t seem to have access. I get this error:

    ‘lib = “C:/Program Files/Microsoft SQL Server/MSSQL13.SQL2016/R_SERVICES/library”‘ is not writable

    This is on my local machine where I am a local admin, and I have tried running SSMS as an administrator. In the past the way I have installed packages is by running RGui as an administrator, but it would be handy to be able to do it right in the R script executed by sp_execute_external_script. Any idea what account needs access to the folder in order to be able to call install.packages right from your R script?

Leave a Reply

%d bloggers like this: