Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

SQL R

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?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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?
Avatar of Leo Torres

ASKER

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
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

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

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)?
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

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.
Event Viewer empty. SP1? don't think that will fix it but I will update what do I have to lose at this point.
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
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

Can you try to restart Launchpad Service again?
yep restarted same error. Installing SP1 now.
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.
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
Just to confirm. Did you also updated the respective packages for MS R Open and R Server?
Not sure how can i check that?
I was looking at the a setup log and i see stand-alone not configured

User generated image
Also just verifying that features are installed.

User generated image
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

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.