R code generated from table in SQL Server won't be executed

Question

R code generated from table in SQL Server won't be executed

I tried to run R code created from database given in How to make R-code from SQL Server database? but I was unable to execute it.

Here is what I tried.

declare @t table(f1 int, f2 int)
insert into @t values (1,2),(3,4),(5,6)

declare @rcode nvarchar(max) = 
concat(
'f1=c(', STUFF( (SELECT concat(',', f1)
            FROM @t
            ORDER BY f1
            FOR XML PATH('')),1, 1, ''),')
f2=c(',  STUFF( (SELECT concat(',', f2)
            FROM @t
            ORDER BY f1
            FOR XML PATH('')),1, 1, ''),')'
        )

select @rcode
EXECUTE sp_execute_external_script
    @language = N'R'
   , @script = N'@rcode'

The code outputs

Msg 39004, Level 16, State 20, Line 0
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.

Msg 39019, Level 16, State 1, Line 0
An external script error occurred:
Error in source(revoScriptConnection) :
revoScriptConnection:1:1: unexpected '@'
1: @
^

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

I also tried to add input and output data to the query but as in https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/rtsql-create-a-predictive-model-r but it still won't run.

How can I see the result of R code that is generated from the data in table?

sql-server

r

asked on Stack Overflow Aug 2, 2017 by

Jaakko Seppälä • edited Aug 3, 2017 by

Jaakko Seppälä

2 Answers

@script parameter expects a string value to be used as an R script. In this case, the string passed is '@rcode'. To assign the value of the variable @rcode use without the quote.

declare @t table(f1 int, f2 int)
insert into @t values (1,2),(3,4),(5,6)

declare @rcode nvarchar(max) = 
concat(
'f1=c(', STUFF( (SELECT concat(',', f1)
            FROM @t
            ORDER BY f1
            FOR XML PATH('')),1, 1, ''),')
f2=c(',  STUFF( (SELECT concat(',', f2)
            FROM @t
            ORDER BY f1
            FOR XML PATH('')),1, 1, ''),')'
        )

select @rcode
EXECUTE sp_execute_external_script
    @language = N'R'
   , @script = @rcode

answered on Stack Overflow Aug 3, 2017 by

Arun Gurunathan

I found that it is easier to pass variables via SQL-statements like this

DROP PROCEDURE IF EXISTS alpha;
GO
CREATE PROCEDURE alpha
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'R-code'
    , @input_data_1 = N'SQL SELECT statement'
    , @input_data_1_name = N'Test'
END;
GO
EXEC alpha;

answered on Stack Overflow Aug 7, 2017 by

Jaakko Seppälä

User contributions licensed under CC BY-SA 3.0