I was recently helping a customer with their R installation. After R was installed successfully, I went to the SQL Server Management Studio and ran the script needed to configure external services
EXEC sp_configure 'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE
GO
RECONFIGURE;
GO
After this script ran successfully, I closed SSMS, and restarted the SQL Server service, noting when I did so that it told me that it was also going to be restarting SQL Server Launchpad, which is installed as part of R Server, was also going to be restarted. Everything looked good until I tried to run my first R command. I went for the super generic script
EXEC sp_execute_external_script @language=N'R'
,@script=N'OutputDataSet <-InputDataSet'
,@input_data_1=N'SELECT 1 as CheckToSeeIfRIsWorking'
WITH RESULT SETS
(
(
[CheckToSeeIfRIsWorking] INT NOT NULL
)
);
Much to my surprise after this I received an error
Msg 39019, Level 16, State 1, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).
Msg 11536, Level 16, State 1, Line 1
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.
I looked in the log files and didn’t find any errors. I checked the configuration manager to ensure that I had some user ids configured in the configuration manager. Nothing seemed to make any difference. Looking online, the only error that I saw which might possibly be close was a different error message about 8.3 naming and the working directory.
Resolving Error Code 0x80070490 Unable to launch the runtime for R in SQL Server
W
hen I saw this error, I first checked the various SQL Log files. I didn’t find any errors. I checked the configuration manager to ensure that I had some user ids configured in the configuration manager. The default 20 users were there as expected. Nothing seemed to make any difference. Looking online, the only error that I saw which might possibly be close was a different error message about 8.3 naming and the working directory. I ran into a similar problem with R and Power BI. R natively doesn’t understand anything but directories which are only 8 characters long. If there are more letters than this in a directory, which is always the case with the default SQL Server directories, internally the directory is read as the first 8 characters and a tilde(~). This particular server also had an installation of SQL Server 2014 on it, and if you looked at the filename, it was impossible to tell which one was which as the directory read like this C:\PROGRA~1\MICROS~2\MSSQL1~1.MSS\MSSQL\EXTENS~1 I created a new directory with short names D:\Rwork and changed the working directory to the new subdirectory Rwork by updating the rlauncher.config file and restarting the SQL Server Launchpad Service. I was really hopeful that this would work. But when I ran the external script listed above, it still did not work. What had I done wrong? I didn’t delete the previous working directory, which was in the default location C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData . When SQL Server creates this subdirectory, it also includes subdirectories for all of the users. When I created the new directory, it didn’t have those subdirectories added. I copied the subdirectories from the old working directory to the new one and tried again. This time, success!
I hope you find this post helpful, as I know I could have used it when I was trying to figure this out. If it saved you time too, drop me a line and let me know.
Yours Always
Ginger Grant
Data aficionado et SQL Raconteur
