Machine Learning: Testing your installation of R and Python in SQL Server 2017

One of the wonderful additions to SQL Server in 2016 was the R language. In SQL Server 2017, Python was also added and the combination of both with SQL Server rebranded to Machine Learning Services.

Why would you want these installed? The most common answer is to enable you to run predictive analytics.

But I’ve found that at many sites, getting R and/or Python installed turned out to be more complicated than it seemed.

NOTE: If you are using SQL Server 2022 or later, see this article instead: https://blog.greglow.com/2024/03/04/fix-getting-r-and-python-to-actually-work-on-sql-server-2022/

Once you have the features added (the in-database options and not the standalone options) for R and Python, you need to enable the execution of external scripts. That’s easy enough:

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

You need to restart the SQL Server service after doing this.

Now you can try to execute this script to see if the features are working:

EXEC sp_execute_external_script @language = N'R', 
                                @script = N'OutputDataSet <- InputDataSet;',
                                @input_data_1 = N'SELECT 1 AS RValue'
WITH RESULT SETS ((RValue int NOT NULL));

EXEC sp_execute_external_script @language = N'Python', 
                                @script = N'OutputDataSet = InputDataSet;',
                                @input_data_1 = N'SELECT 1 AS PythonValue'
WITH RESULT SETS ((RValue int NOT NULL));
GO

If all is good, you’ll see that it worked. Both values would be returned.

If you haven’t changed the default SQL Server configuration though, I don’t think that’s what you’ll see. More likely, you’ll see this:

Msg 39012, Level 16, State 1, Line 4 Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime.

STDERR message(s) from external script: Fatal error: cannot create ‘R_TempDir’

So, why does that happen? It’s SQL Server’s attempt at telling you that R doesn’t like paths with spaces in them, and that’s what the default configuration has. (I have no idea why).

If you open Notepad (or your favorite editor) as an administrator, navigate to this file:

The file rlauncher.config holds the configuration for the R feature.

That path for the working directory isn’t going to work. Now you can change the values in it to 8.3 filenames like they have for RHOME in the first line, or, my preference, point it to a different temp folder.

I then make sure that C:\Temp actually exists, and that there’s an ExtensibilityData folder under that. I then copy in all the folders from the original folder:

These are used for different processes running from the SQL Launch Pad.

Then restart both the SQL Server service and the SQL Launch Pad service and try your script again.

If you still have no luck, chances are high that the security isn’t correct for the folders. Ensure that the SQL Launch Pad service account has full control on the ExtensibilityData folder and all sub-folders. By default, the service account will be MSSQLLaunchPad but you can check it in SQL Server Configuration Manager.

I’d restart both services again just for good luck, and then hopefully you’ll see a response from both the queries.

Then you’re ready to start investigating Machine Learning in SQL Server.

2018-05-16