Saturday, July 23, 2016

Problem enabling R Service in SQL Server 2016

In my previous post I have compeltely 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 followwing 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:


The second line in the above statement does not seem to have any meaning since Reconfigure is the second line of the executed statement.

Now If I ignore the inconsistency and go ahead and run this suggested statement:
exec sp_execute_external_script  @language =N'R',   
@input_data_1 =N'select 1 as hello'   
with result sets (([hello] int not null));   

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

Hence, I believe there is a problem.

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...