Share via

SQL Server: can not execute queries with custom R

Olga Larina 26 Reputation points
2021-08-31T09:55:27.217+00:00

I'm using SQL Server with Machine Learning Services and Language Extensions for R. It works well. But I need to update R and follow tutorial: Install an R custom runtime for SQL Server, https://learn.microsoft.com/en-us/sql/machine-learning/install/custom-runtime-r?view=sql-server-ver15&pivots=platform-windows.
After all setups, I get an error after executing the query:
EXEC sp_execute_external_script
@language =N'myR',
@script=
N'print("Hello RExtension!");'

Started executing query at Line 1
Msg 39004, Level 16, State 20, Line 0
A 'myR' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Total execution time: 00:00:00.891

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
Accepted answer
  1. Erland Sommarskog 107.2K Reputation points
    2021-09-01T21:23:11.477+00:00

    Sorry, I failed to pay attention that it was this new custom-extension thing in SQL 2019.

    I went through the steps in the article, and when I tested your script, I got this output: 

    STDOUT message(s) from external script: 
[1] " ��Hello RExtension! ��"

    This question marks indicates that there is an encoding problem, but I got no error message.

    I can't say what may be wrong in your case. I tried the error code you got in the Error Lookup utility, and it seems to mean operation aborted.

    I noticed that there were quite a few steps to get this running, so may want to go back to check if you made a mistake on the way.

    Also, you seem to have the original R installed, which I don't have on this instance. The introduction of the article says "Do not select R" in the beginning. I don't know if there can be a conflict.

    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2021-08-31T21:57:24.277+00:00

    The following script works for me: 

    EXEC sp_execute_external_script
@language =N'R',
@script=
N'print("Hello RExtension!");'

    Note that I have corrected the value for the @language oarameter.

    0 comments
  2. Olga Larina 26 Reputation points
    2021-09-01T06:25:23.833+00:00

    Hi, Erland!

    Thank you for your answer. Indeed R - default version 3.5.2 - works well.
    But the idea is to update R (tutorial describes the process). And a new version - myR, 4.1.1 - doesn't work.

    0 comments
  3. Olga Larina 26 Reputation points
    2021-09-10T15:09:47.83+00:00

    Hi Erland!

    Thank you for testing it! I tried to start from the beginning and removed SQL Server, then installed without R. But still the at the end. I'll appreciate any tips.

    Started executing query at Line 1
    Msg 39004, Level 16, State 20, Line 0
    A 'myR' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
    Total execution time: 00:00:00.439

    0 comments
  4. Olga Larina 26 Reputation points
    2021-09-10T15:27:30.32+00:00

    Oh! It was a problem with environmental variable.
    Now I have the same encoding problem as you.
    How can I fix that?

    STDOUT message(s) from external script:
    [1] " ��C:/Program Files/R/R-4.1.1 ��"
    [1] " ��C:/Program Files/R/R-4.1.1/bin/x64/R ��"
    ��_ ��
    ��platform �� ��x86_64-w64-mingw32 ��
    ��arch �� ��x86_64 ��
    ��os �� ��mingw32 ��
    ��system �� ��x86_64, mingw32 ��
    ��status �� �� ��
    ��major �� ��4 ��
    ��minor �� ��1.1 ��
    ��year �� ��2021 ��
    ��month �� ��08 ��
    ��day �� ��10 ��
    ��svn rev �� ��80725 ��
    ��language �� ��R ��
    ��version.string �� ��R version 4.1.1 (2021-08-10) ��
    ��nickname �� ��Kick Things ��
    [1] " ��Hello RExtension! ��"