0

Trying to configure MLS on SQL Server 2017, but when running a very basic external script like so:

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

I get this error:

Msg 39021, Level 16, State 1, Line 1 Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime. Msg 39019, Level 16, State 2, Line 1 An external script error occurred: Unable to launch the runtime. ErrorCode 0x80070032: 50(The request is not supported.).

And if I look at the log EXTLAUNCHERRORLOG I see:

2020-12-29 17:53:49.554 SetCpuRateCap failed with error code 0x80070032.

I can't find a reference to this error anywhere, and am very perplexted. Tried all kinds of things (checking permissions, turning the resource governor off, updating to latest CU, reinstalling MLS, etc). We have a similar server that is running on same Azure platform (same size, W2012R2, same memory/cpu config), configured about the same time and it seems to not have this issue. This happens with both R and Python services.

Any help would be appreciated.

user3258158
  • 1
  • https://stackoverflow.com/questions/33473612/unable-to-launch-runtime-for-r-script "This issue is related to the working directory for the R Services and if you are facing this error, the first thing to do is check the path for the working directory for R Services. If you didn't change the default file locations during the R Services installation then there is a good chance that the R Services working directory path has folder names with spaces and it's necessary to change the working directory to a path with no spaces." – Mitch Wheat Dec 30 '20 at 02:54
  • https://www.mssqltips.com/sqlservertip/4557/unable-to-communicate-with-the-runtime-for-r-script-in-sql-server/ – Mitch Wheat Dec 30 '20 at 02:57
  • Thanks. We tried those fixes but to no luck. There are no spaces in the R paths and we tried the /uninstall and /install to no avail. We did however figure out that if you turn the resource governor OFF it works. If you turn it ON, but have the Maximum CPU % at anything less than 100% it does not. So, seems like something to do with the resource governor, and, as it says, setting the CPU rate for some reason. – user3258158 Dec 30 '20 at 16:25
  • I found this in the SQL logs: `Resource governor reconfiguration encountered an issue (HRESULT code : 0x8007000e, reason: No memory for connecting LaunchPad process), while sending active external resource pool ids to launchpad. This will not fail reconfigure.` – user3258158 Dec 30 '20 at 16:36
  • https://dba.stackexchange.com/questions/213857/how-to-grant-more-memory-to-run-r-processes-in-sql-server-2016-r-services – Mitch Wheat Dec 31 '20 at 01:04
  • Tried adjusting various settings with the governor but even with plenty of memory allocated as soon as a drop it from 100% CPU to anything less than 100% CPU it throws that error. – user3258158 Jan 04 '21 at 02:14

1 Answers1

0

I have a solution we used today changing from R 3.3.3 and python 3.5.2 to R 3.5.2 and Python 3.7.1.

SQL 2017 CU22 installs the higher value of R & Python, so you have to run an exe to tell SQL to use the higher R & Python versions that were installed on disk.

The fix that worked for us :

(1) Create a new directory e.g. D:\MLSTEMP

(2) Create subdirectories D:\MLSTEMP\ < SQL_instance >00 and D:\MLSTEMP\ < SQL_instance >01

(3) Make sure the local MSSQLLaunchpad service, Everyone group & SQL service account has full access to both the D:\MLSTEMP and the sub dirs.

Note : the < > brackets are not part of the directory name

Note: launchpad service name is : NTService\MSSQLLaunchpad$ < instance_name >

(4) Go to your

D:\Program Files\Microsoft SQL Server\MSSQL14.SQL001\MSSQL\Binn\pythonlauncher.config

and

D:\Program Files\Microsoft SQL Server\MSSQL14.SQL001\MSSQL\Binn\rlauncher.config
files and alter the WORKING_DIRECTORY setting in each file to be WORKING_DIRECTORY=D:\MLSTEMP

(5) Restart the MSSQLLaunchpad service on the box.

Interestingly, the ****01 directory is the one that's used, not the ****00 dir.

It should now work. You may have to play with permissions a bit.

HTH.

steve
  • 395
  • 2
  • 11