I am helping some people learn Data Science and we are having a ton of fun! There are lots of things to remember. So I am noting things here, in case I forget!
We noted the following error message, when we saw that R was not running on our SQL Server 2017 install:
‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.
Here is the longer version:
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.
Msg 11536, Level 16, State 1, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.
Grr! What’s happened here? We had installed R as part of the SQL installation, and we had run the command to enable it, too. In case you are wondering, here is the command:
EXEC sp_configure ‘external scripts enabled’, 1
RECONFIGURE WITH OVERRIDE
So what happens next? Initial things to check:
Is R Server installed properly along with SQL Server? Here are some guidelines to help you.
Is the Launchpad service running? One of my colleagues and friends Tomaž Kaštrun wrote a nice article on SQL Server Central. If not, this could be due to a lack of permissions in being able to start the service.
Did you restart the MSSQL Service on the machine? This will also restart the Launchpad service as well. If you didn’t restart the service, you will need to do that so it can pick up the results.
Once R is running properly, you can check it by using the following command, borrowed from the official installation guide over at Microsoft:
EXEC sp_execute_external_script @language =N’R’,
@script=N’
OutputDataSet <- InputDataSet;
‘,
@input_data_1 =N’SELECT 1 AS RIsWorkingFine’
WITH RESULT SETS (([RIsWorkingFine] int not null));
GO
If that returns a 1, then you are all set! To prove it works properly, you can retrieve the world famous Iris dataset using the following command, borrowed from the official documentation on sp_execute_external_script:
DROP PROC IF EXISTS get_iris_dataset;
go
CREATE PROC get_iris_dataset
AS BEGIN
EXEC sp_execute_external_script @language = N‘R’ , @script = N‘iris_data <- iris;’ , @input_data_1 = N” , @output_data_1_name = N‘iris_data’ WITH RESULT SETS ((“Sepal.Length” float not null, “Sepal.Width” float not null, “Petal.Length” float not null, “Petal.Width” float not null, “Species” varchar(100)));
END;
GO
Once you’ve created the command, execute the following SQL command and you will see the iris dataset:
exec get_iris_dataset
You’re all set! Enjoy R!