question

OlgaLarina-3330 avatar image
0 Votes"
OlgaLarina-3330 asked MarkPowers-9702 published

SQL Server: can not execute queries with custom R

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://docs.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-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OlgaLarina-3330,

Could you share us more related error message form SQL server error log? The error message that you offered is a general error message.

0 Votes 0 ·
  1. In 'Messages' I get only the message I sent:
    Msg 39004, Level 16, State 20, Line 0
    A 'myR' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.

    Completion time: 2021-09-01T10:13:41.7235009+02:00

  2. In SQL Server log:

    Date,Source,Severity,Message
    09/01/2021 10:13:41,spid59,Unknown,SQL Server received abort message and abort execution for major error : 1 and minor error : 1
    09/01/2021 10:12:15,spid55,Unknown,SQL Server received abort message and abort execution for major error : 1 and minor error : 1
    09/01/2021 07:33:34,spid29s,Unknown,This instance of SQL Server has been using a process ID of 7304 since 2021-08-31 11:42:07 (local) 2021-08-31 09:42:07 (UTC). This is an informational message only; no user action is required.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlgaLarina-3330 avatar image
0 Votes"
OlgaLarina-3330 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlgaLarina-3330 avatar image
0 Votes"
OlgaLarina-3330 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlgaLarina-3330 avatar image
0 Votes"
OlgaLarina-3330 answered MarkPowers-9702 published

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm glad to hear that you were able to find the solution to the first problem.

When it comes to the encoding issue, I don't have any answer. I suspect that to understand what is going on, you need some knowledge about input/output in R, but I don't know much about R.

(Sorry for the late reply, but I was out of town.)

0 Votes 0 ·

So what was the problem with the environment variable? Was it PATH or R_HOME?

0 Votes 0 ·