SQL Server: can not execute queries with custom R

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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! ��"