none
how to create a stored procedure for SQL for the function CP and CPK using external script 'R' where it displays and error. RRS feed

  • Question

  • EXEC   sp_execute_external_script
          @language = N'R'
         ,@script = N' x <-  rweibull(30, 2, 8) +100;
    cp(x, "weibull", lsl = 100, usl = 117)
    OutputDataSet <- as.data.frame(x);'
         , @input_data_1 = N' ';

    ***********************************

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

    Monday, July 15, 2019 5:59 AM

All replies

  • IIRW SQL Server runs your R script using a separate account with lowered privileges, for security reasons. In particular, this is not the same as your own user account. So if you installed your packages under your user directory, the script won't be able to find them,....

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 15, 2019 7:14 AM
    Moderator
  • I have been using MS SQL 2019 Server with all the R and Python packages installed from the same installation,and it is functioning fine for the rest functions but except for CP and CPK and ML Algorithms.
    Monday, July 15, 2019 8:50 AM
  • Hi Sowmya1994,

    As Uri mentioned, SQL Server runs your R script using a separate account with lowered privileges, for security reasons. In particular, this is not the same as your own user account. So if you installed your packages under your user directory, the script won't be able to find them.

    A fix is to install the packages in a separate, globally readable directory. 

    Please refer to this similar thread. Hope it could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 16, 2019 8:35 AM
  • Hi Cathy & Uri,

    Thanks for the support,I have installed all that is necessary but as that function outputs two datatypes I am unable to capture the output into variables.

    Output datatype:

    1.image

    2.dataframe

      Please check this code in R-studio for detailed view.

           x=rweibull(30,2,8)+100

           cp(x, "weibull", lsl = 100, usl = 117)
     

    Please check this code in SQL-2019 for detailed view.

    EXECUTE sp_execute_external_script
          @language = N'R'
        , @script = N'library(qualityTools)
     x <- rweibull(30, 2, 8) +100;
    cp(x, "weibull", lsl = 100, usl = 117);

    But here As I am not sure how to capture it using WITH RESULT SETS() or OutputDataset as.

    And to mention I have even tried changing the image output to binary which didnt work either.

    code:

    EXECUTE sp_execute_external_script
           @language = N'R'
         , @script = N'library(qualityTools)
      x <- rweibull(30, 2, 8) +100;

       (output dataset as.data.frame(cp(x, "weibull", lsl = 100, usl = 117))),
       (df <- cp(x, "weibull", lsl = 100, usl = 117); 
    image_file = tempfile();
    jpeg(filename = image_file, width=500, height=500);
    hist(cp); 
    dev.off();
    OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));
    '
    ,@input_data_1 = N'(cp(x, "weibull", lsl = 100, usl = 117));'
    ,@input_data_1_name = N'inputDataSet'
    ,@output_data_1_name = N'OutputDataset'
         WITH RESULT SETS ((plot varbinary(max)));

    Can you please help how to deal with this issue.

    Wednesday, July 17, 2019 5:12 AM
  • I do not think it is possible to assign the result set  to the variable , I think what you can do is load the data into a table by using INSWRT ...EXEC ,however  you won;t be able specify a WITH RESULT SETS

    create table #t (plot varbinary(max))

    INSERT INTO #t

    EXECUTE sp_execute_external_script
           @language = N'R'
         , @script = N'library(qualityTools)
      x <- rweibull(30, 2, 8) +100;

       (output dataset as.data.frame(cp(x, "weibull", lsl = 100, usl = 117))),
       (df <- cp(x, "weibull", lsl = 100, usl = 117); 
    image_file = tempfile();
    jpeg(filename = image_file, width=500, height=500);
    hist(cp); 
    dev.off();
    OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));
    '
    ,@input_data_1 = N'(cp(x, "weibull", lsl = 100, usl = 117));'
    ,@input_data_1_name = N'inputDataSet'
    ,@output_data_1_name = N'OutputDataset'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 17, 2019 6:37 AM
    Moderator