Leo Torres
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.
I looked online and everyone says just turn on service but my service is already running. What else could it be?
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.
I looked online and everyone says just turn on service but my service is already running. What else could it be?
ASKER
Here is code I am running
My error is listed on original post
-- 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
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
ASKER
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.
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)?
Which build version of SQL Server are you using (SELECT @@version)?
ASKER
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)
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.
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.
ASKER
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\M SSQL\Log\E xtensibili tyLog
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\M
ASKER
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.
Can you try to restart Launchpad Service again?
ASKER
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.
Let me know if it helped.
ASKER
Update complete
still same error
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)
still same error
Just to confirm. Did you also updated the respective packages for MS R Open and R Server?
ASKER
Not sure how can i check that?
ASKER
OK. so looks like the issue was with the account running Launchpad did not have access to certain locations
New error
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thank you sorry for late response.
If so, can you post the code you're trying to run?