SQL Server 2016 – Using sp_execute_external_script

sql server

I'm trying to execute a new stored proc in 2016 named sp_execute_external_script. I first need to enable external scripts as follows:

sp_configure 'external scripts enabled', 1;

After doing this, I see this message:

Configuration option 'external scripts enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.

I then execute RECONFIGURE and see a message saying, "Command(s) completed successfully".

I then attempt to execute the following, to see if R is working correctly:

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));
go

When I do so, I see the following error:

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 3]
'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.

Any ideas why?

Best Answer

After turning on external scripts, you'll need to restart SQL Server so that when you run

sp_configure 'external scripts enabled';

it returns a run_value of 1:

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
external scripts enabled            0           1           1            1

Documentation: External Scripts Enabled server configuration option

Related Question