We help IT Professionals succeed at work.

SQL R

Leo Torres
Leo Torres asked
on
700 Views
Last Modified: 2018-04-19
Hello, I just installed SQL 2016 with R. The LaunchPad service is running and the user  SQLRUserGroup is a sysadmin on server. yet I am still geting this error.

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.  
Msg 11536, Level 16, State 1, Line 22
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.

Open in new window


I looked online and everyone says just turn on service but my service is already running. What else could it be?
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The error occurs when you're trying to run sys.sp_execute_external_script procedure?
If so, can you post the code you're trying to run?
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
Here is code I am running
-- and run statistical correlation between
DECLARE @RScript nvarchar(max)
SET @RScript = N'
				 library(Hmisc)	
				 mydata <- InputDataSet
				 all_sub <- mydata[2:3]
				 c <- cor(all_sub, use="complete.obs", method="pearson") 
				 t <- rcorr(as.matrix(all_sub), type="pearson")
			 	 c <- cor(all_sub, use="complete.obs", method="pearson") 
				 c <- data.frame(c)
				 OutputDataSet <- c'

DECLARE @SQLScript nvarchar(max)
SET @SQLScript = N'SELECT
						 TableName
						,RowCounts
						,UsedSpaceKB
						,TimeMeasure
						FROM DataPack_Info_SMALL'

EXECUTE sp_execute_external_script
	 @language = N'R'
	,@script = @RScript
	,@input_data_1 = @SQLScript
	WITH result SETS ( (
						 RowCounts VARCHAR(100)
						,UsedSpaceKB  VARCHAR(100)
						)
					 );

GO

Open in new window



My error is listed on original post
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Did you try to check if it runs WITH RESULTS SETS UNDEFINED?
EXECUTE sp_execute_external_script
	 @language = N'R'
	,@script = @RScript
	,@input_data_1 = @SQLScript
WITH result SETS UNDEFINED

Open in new window

Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
With your change I still get Launchpad Error

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. 

Open in new window

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Ok, looks like isn't an issue with your script but the Launchpad service.
Which build version of SQL Server are you using (SELECT @@version)?
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
Resultset
Microsoft SQL Server 2016 (RTM-GDR) (KB3194716) - 13.0.1722.0 (X64)   Sep 26 2016 13:17:23   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

Open in new window

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You can try to apply SP1. If it is a bug, SP1 should fixed it.
Otherwise you can try to restart the Launchpad service and then check in the Event Viewer for any warning or error relative to this service.
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
Event Viewer empty. SP1? don't think that will fix it but I will update what do I have to lose at this point.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You have some issue with the Launchpad.
You can always check the specific logs for R Services. By default they can be found in C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER\MSSQL\Log\ExtensibilityLog
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
log content

2017-03-15 16:38:50.365	User Config data file: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\SQLRConfigData

2017-03-15 16:38:50.366	User Config key file: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\SQLRConfig
2017-03-15 16:38:50.622	Security Context Manager is initialized successfully.
2017-03-15 16:38:50.623	Satellite Session Manager is initialized successfully.
2017-03-15 16:38:50.632	Negotiated Runtime Version for launcher RLauncher.dll is 1
2017-03-15 16:38:50.632	Got API for launcher RLauncher.dll with version 1
2017-03-15 16:38:50.632	Successfully loaded launcher RLauncher.dll and negotiated version
2017-03-15 16:38:50.639	Successfully initialized launcher RLauncher.dll
2017-03-15 16:38:50.639	Successfully found the launcher interfaces for launcher RLauncher.dll
2017-03-15 16:38:50.639	Successfully registered the launchcontext for launcher RLauncher.dll
2017-03-15 16:38:50.639	Successfully registered the launcher RLauncher.dll for the scriptType 1
2017-03-15 16:38:50.639	The SQL group account Name is MSSQLSERVER.
2017-03-15 16:38:50.793	MSSQLLaunchpad service successfully started.

Open in new window

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Can you try to restart Launchpad Service again?
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
yep restarted same error. Installing SP1 now.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Ok. I have SP1 installed but when I had only RTM build I also didn't face this error.
Let me know if it helped.
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
Update complete

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) 

Open in new window


still same error
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Just to confirm. Did you also updated the respective packages for MS R Open and R Server?
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
Not sure how can i check that?
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
I was looking at the a setup log and i see stand-alone not configured

log
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
Also just verifying that features are installed.

Here is the install. Looks fine to me.
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
OK. so looks like the issue was with the account running Launchpad did not have access to certain locations

New error
Msg 39004, Level 16, State 20, Line 4
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 4
An external script error occurred: 
Error in library(Hmisc) : there is no package called 'Hmisc'
Calls: source -> withVisible -> eval -> eval -> library

Error in ScaleR.  Check the output for more information.
Error in eval(expr, envir, enclos) : 
  Error in ScaleR.  Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
Msg 11536, Level 16, State 1, Line 24
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.

Open in new window

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Leo TorresSQL Developer
CERTIFIED EXPERT

Author

Commented:
This was fixed it was the SQL service account running R did not have enough rights on the folder R runs from.

Thank you sorry for late response.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.