4

We have just installed SQL Server Enterprise 2016 CTP 3.3 on a server that already had SQL 2012 Standard installed. We installed SQL2016 as a Named Instance and have been trying to get the R integration working, but we are having problems running the external scripts from management studio.

We walked through the Install the R Packages described here: https://msdn.microsoft.com/en-us/library/mt590809.aspx, And we have manually run the Post-Installation R Configuration steps listed here: https://msdn.microsoft.com/en-us/library/mt590536.aspx

At this point, we are able to connect to SQL from RStudio and manipulate data locally or on the server. However, we are not able to specify R script in Management Studio. When we try to run an R script in TSQL, we get the error message:

Msg 39011, Level 16, State 1, Line 1 SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

We have tried several of the sample R scripts including the following:

  1. execute sp_execute_external_script @language = N'R' , @script = N' mytextvariable <- c("hello", " ", "world"); OutputDataSet <- as.data.frame(mytextvariable);' , @input_data_1 = N' SELECT 1 as Temp1' WITH RESULT SETS (([col] char(20) NOT NULL));
  2. execute sp_execute_external_script @language = N'R' , @script = N' OutputDataSet <- InputDataSet;' , @input_data_1 = N' SELECT * FROM MyData;' WITH RESULT SETS (([NewColName] int NOT NULL));
  3. execute sp_execute_external_script @language = N'R' , @script = N' SQLOut <- SQLIn;' , @input_data_1 = N' SELECT 12 as Col;' , @input_data_1_name = N'SQLIn' , @output_data_1_name = N'SQLOut' WITH RESULT SETS (([NewColName] int NOT NULL));

All of them result in the same error messages about not being able to communicate with the LaunchPad service.


Here are the configuration options we have verified seem to be configured correctly:

  • We can see that “external scripts enabled” has a value of 1 when running “exec sp_configure”
  • We can see the Windows User Group “SQLRUserGroupSQL2016” has been created in “Local Users and Groups”
  • We can see the 20 Windows Users created for R processing in “Local Users and Groups”
  • We can see that the Database Role “db_rrerole“ was created correctly on master db.
  • We can see the Extended Stored Procedures “xp_ScaleR_ …” have been correctly created on master db.
  • We have verified that the SQL Server Service for our Instance is running along with the SQL Agent Service and SQL LaunchPad Service.
    • They are all logged in as the same Windows Domain User Account, and that user has been added to the Administrators Group for the server.
  • We have tried restarting the above services many times as well as rebooting the server.
  • We have tried connecting to Management Studio both using Windows Auth with a Domain Account that is in the Database Role “sysadmins”, and as a local SQL User Account that is in the Database Role “sysadmins”
  • We manually added those users to the “db_owner” and “db_rrerole” roles on the master db (just to be sure)

Don’t know what else to check…

  • this seems related.dba.stackexchange.com/questions/120205/… – TheGameiswar Feb 26 '16 at 15:28
  • @TheGameiswar - no, I did notice that one. In that post, his LaunchPad service was not running – Stephen Feb 26 '16 at 17:16
  • @Stephen, can you let me know how you verified that the Extended Stored Procedures “xp_ScaleR_ …” have been correctly created on master db? I'm facing an error that says "couldn't find stored procedure - master xp_ScaleR_init_job" – Minu Jun 19 '16 at 0:03
  • @Minu - its been a while, but I verified that the xp_ScaleR_ procedures were actually present in master --> Programmability --> Extended Stored Procedures. When I view properties for the stored procedures, I can see that Execute permission has been granted to the role: db_rrerole. I think that's what I based my statement on that they "seem to be configured correctly" – Stephen Jun 20 '16 at 12:37
  • @Stephen, My SQL Server 2016 shows that the stored procedure "xp_ScaleR_init_job" is actually not present. Any ideas how to get this procedure? – Minu Jun 21 '16 at 2:14
1

UPDATE: It looks like R Services setup is significantly simpler from SQL 2016 RC2 onwards. See here.

This post applies to early CTPs of SQL Server 2016 where additional setup was required for R.

I had this error when I upgraded from CTP3.2 to 3.3. The first thing to check is, if you have a named instance, did you use that name when you ran the post-installation script:

"...rxLibs\x64\registerRext.exe" /install /instance:sql2016

I assume so because you've got the db_rrerole but if not, uninstall using the uninstall switch and try again using your instance name:

"...rxLibs\x64\registerRext.exe" /uninstall

Monitor the output for errors during the install or consider re-directing the output to a file so you can review it afterwards, eg:

"...rxLibs\x64\registerRext.exe" /install /instance:yourNamedInstance > mylog.txt

Check through the log and report any errors you find. If the script succeeds, the Launchpad service should be running and you can check this by running services.msc from the command-line. Here's my service running for the default instance:

Launchpad service is running

Yours should say SQL2016 where mine says MSSQLSERVER. If that's all ok you can open up the the config file, eg from the command-line:

notepad "%programfiles%\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher.config"

You will have to change the path above, but the file should be in the Binn directory of your SQL Server install. Inspect the config file and make sure the paths are correct / look sensible. I found initially that there were version numbers on the paths from multiple installs eg ( MSSQLSERVER1000 ) and basically this was interfering with the R install. You can check registry keys HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLLaunchpad$SQL2016 and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server to see if they are consistent but I strongly advise against making changes to your registry, unless you can afford to lose the machine / VM and have to do a full re-install of Windows etc.

I basically did a full uninstall of SQL Server (including a somewhat brutal registry clean-up) and then re-installed a default instance and it worked. I've since resolved not to mix CTPs and set up clean VMs for each one. Also remember CTPs are basically beta products so you should not install them on any servers you value - hopefully this is a test box or non-production VM?

My best advice to you is honestly just use a VM for the CTPs because they are not designed to play well with other versions or even CTP upgrades. I use VirtualBox on my laptop which is fine most of the time. If you have an MSDN subscription (or even a trial one) you can set up a free VM with the latest CTP and throw it away when you're done.

Good luck!

  • So, after going rounds with this again yesterday, we decided to give it a shot and install a VM on that test server to install SQL2016 CTP 3.3 as the default instance on the box... Walked through the same steps to install and configure, and executing R scripts now works as expected. The only difference I can see is that we didnt change the services to run as a domain account yet. Everything else is the same except it is now the default instance rather than a named instance and theres no other SQL installed. Doesnt really tell me what was wrong, but its working now. Thanks for your help! – Stephen Mar 1 '16 at 12:24
1

The service account of the LaunchPad service needs to be a local admin. Check if the service account is in the local Administrators group on the server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.