T-SQL with external script execution for in-database R integration

Question

I'm trying to convert a scaleR script into T-SQL (with external_script execution) for in-database R analytics in SQL Server. The scaleR script that I'm working with is on the DeepDive Data Science Tutorial on Fraud data by MSDN.

All my data is in SQL Server now (from the tutorial), and all I want to do is query this table to get summary using the rxSummary scaleR function (in-database).
This is my attempt:

exec sp_execute_external_script 
@language = N'R', 
@script = N' 
sumOut <- rxSummary( 
formula = ~gender + balance + numTrans + numIntlTrans + creditLine, 
data = ccFraud 
) 
', 
@input_data_1 = N'select * from [DeepDive].[db_datareader].[ccFraudSmall]', 
@input_data_1_name = N'ccFraud', 
@output_data_1_name = N'summary' 
with result sets ((summary varchar(max) not null));

But this throws an error:

STDOUT message(s) from external script: 
Rows Read: 10000, Total Rows Processed: 10000, Total Chunk Time: Less than .001 seconds 
Computation time: 0.000 seconds. 
Msg 11536, Level 16, State 1, Line 5 
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.

Any idea how to deal with this?

Am I missing a step?

Arun Gurunathan · Accepted Answer · 2016-06-23 22:50:36Z

1

In your example, there is a mismatch between the R script and SQL parameter definitions. The output_data_1_name parameter value specifies the name of the R dataframe that is being returned from R to SQL Server. But there is no value assigned to summary in the R script. Hence you are getting a 0 results error. See sp_execute_external_script document for description and example.

If you are just trying to display the summary as messages, then you can add print(sumOut) to the R script.

If you want to return data to SQL Server, you would need to build an R dataframe and assign to the output_1_data.

edited Jun 23, 2016 at 22:50

answered Jun 23, 2016 at 21:46

Arun Gurunathan

3061 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Raj Over a year ago

Thanks, that worked to display the summary results. But when I want to save Summary to SQL Server, I tried adding summary <- data.frame(sumOut) to the R Script, it throws an error that rxSummary cannot be coerced to a data frame.

Arun Gurunathan Over a year ago

Are you trying to store the rxSummary object as a binary in SQL Server? Try using summary <- serialize(sumOut, NULL);

Raj Over a year ago

This is my code now:

@script = N' 				sumOut <- rxSummary( 									formula = ~gender + balance + numTrans + numIntlTrans + creditLine,  									data = ccFraud 									) 				print(sumOut) 				summary <- serialize(sumOut, NULL); 				',  	@input_data_1 = N'select * from [DeepDive].[db_datareader].[ccFraud10]',  	@input_data_1_name = N'ccFraud', 	@output_data_1_name = N'summary' 	with result sets ((summary varbinary(max) not null));

Shows this error: The output dataset must be of type data frame; consider using data.frame() to convert it.

Raj Over a year ago

Also still shows this error: 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.

Arun Gurunathan Over a year ago

Refer to the Train and Save a Model using T-SQL tutorial for an example of how to store a serialized R object to SQL Server table

Collectives™ on Stack Overflow

T-SQL with external script execution for in-database R integration

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related