Errors upgrading to SQL Server 2016 and using R

Summary:

After upgrading our SQL 2014 instances to 2016, I was naturally curious to try out some R scripting that is now natively supported. So I went over to Microsoft’s tutorials to start getting setup and run my first ‘Hello World’ R script from Management Studio.

Right away I ran into a few errors and troubleshooting them was difficult. Like many other DBAs, I have no real R experience and this is a new frontier for SQL Server. When it came to researching the error messages, since the release candidates had been out for a while, there were a lot issues with the same error messages that weren’t relevant to the RTM version scattered across blogs, Stack Exchange, and the MS forums. There were a bunch that didn’t make sense and still others that didn’t work at all for me. It was an amalgamation of a few that ended up being the correct solution.

The Fix

Turn on R via sp_configure:

    sp_configure 'external scripts enabled', 1;
    GO

    RECONFIGURE;

To verify this turned on correctly, check for a run_value of 1 in the return of:

    Exec sp_configure  'external scripts enabled'

Restart the SQL Server and Launchpad services.

At this point I received the following error when trying to run an R script:

> Msg 39012, Level 16, State 1, Line 1
> 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 after some researching I found this was due to there being spaces in the working directory that is by default specified for R. Apparently R doesn’t get along well with spaces in the filename for its ExtensibilityData folder.

Edit the rlauncher.config file (located in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn) to ensure the WORKING_DIRECTORY= destination has no spaces in it. In my case, I switched it to the root of my data drive:

After restarting both services again, a new error appeared:

> 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 1, Line 1
> An external script error occurred: Unable to launch the runtime.
> ErrorCode 0x80070490: 1168(Element not found.).

In addition to pointing to the new folder, the original folder’s subdirectories must also all be copied to the new location (even though they’re probably empty at this point). After moving them, my ExtensibleData folder had these:

With a final restart of the services, the Hello World R script finally executed:

Tweet Share