Error (HRESULT 0x80072746) when executing the sp_execute_external_script part of the CSharp demo.

[digdugd]

I went through the steps in the Dotnet example and I am getting the message below when executing the "sp_execute_external_script" part of the demo.

Msg 39004, Level 16, State 19, Line 25 A 'Dotnet' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80072746.

Message in Event Viewer: SQL Server encountered error 0x800704d4 while communicating with the 'Dotnet' runtime for request id: 6B9D5162-000F-4ECC-850C-39844B3F018C. Please check the configuration of the 'Dotnet' runtime.

Searching online I found some info relating to the R and Java extensions but answers were related to supporting libraries for the underlying language.. so didn't get much insight into what might be missing in this Dotnet setup.

[Aniruddh25]

Thank you for trying it out, let us try to figure out what could be going wrong.

1. Are you trying this with SQL Server 2019 CU3 + ?
2. In the MSSQL\ExternalLanguages folder do you see the language files being created ? Similar to the following path :

3. Can you also verify if the external library is created ?

4. Can you please try using the -debug.zip version to see if we get more information after running sp_execute_external_script ? https://github.com/microsoft/sql-server-language-extensions/releases/download/dotnet-csharp-v1.0.0/dotnet-core-CSharp-lang-extension-windows-debug.zip

[digdugd]

Thanks for the reply!

1) Yes using the latest CU for SQL 2019.

2) I was wondering about those folders. When I run the TSQL for CREATE EXTERNAL LANGUAGE and CREATE EXTERNAL LIBRARY nothing shows up in those folders UNTIL I try to execute sp_execute_external_script and then I do see all the files show up under a similar path "MSSQL\ExternalLanguages\15\65542" for the CSharp Extension

3) When the above folders and files show up I do get a folder structure under the ExternalLibraries folder of "15\65542".. but no file RegexSample.dll. It looks like this might be a clue to what is going on. I assume if no dll makes it to this folder things aren't going to run.

4) I did drop and re-create the external language with the debug version.. and dropped and re-created everything.. with the same results.

Any ideas on what could cause what I see in #3?

I came across this page.. https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-library-transact-sql?view=sql-server-ver15 where I noticed this line..

_After the library has been successfully uploaded to the instance, a user executes the sp_execute_externalscript procedure, to install the library.

EXEC sp_execute_external_script @language =N'R', @script=N'library(customPackage)'

This gives me the same error message.. which makes sense if the dll didn't get properly loaded.

Thanks again!

[Aniruddh25]

nothing shows up in those folders UNTIL I try to execute sp_execute_external_script

This is by design.

I assume if no dll makes it to this folder things aren't going to run.

That's true.

This gives me the same error message.. which makes sense if the dll didn't get properly loaded.

Have you tried installing a valid R library e.g. lazyeval by replacing customPackage with the library name when you ran the following command ?

CREATE EXTERNAL LIBRARY customPackage
FROM (CONTENT = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\customPackage.zip') WITH (LANGUAGE = 'R');

Overall, it seems to me the Create external library feature for any external language is failing on your machine due to some permissions issue. Could you potentially try setting this up on a VM, please ?

[digdugd]

I am not familiar with R, but if you can point me to a downloadable library package and sample code that would execute that library to test if it runs, I can certainly try that. Below is the result of SELECT * FROM sys.external_languages just incase it gives any clues.

Also, I noticed that many of the examples show the CREATE EXTERNAL LIBRARY using a zip file for the content.. but the example for the regex library is just the DLL.. could this be the issue?

[GarrettBeatty]

I am not familiar with R, but if you can point me to a downloadable library package and sample code that would execute that library to test if it runs, I can certainly try that. Below is the result of SELECT * FROM sys.external_languages just incase it gives any clues.

Here are the steps for R

1. https://docs.microsoft.com/en-us/sql/machine-learning/install/custom-runtime-r?view=sql-server-ver15&pivots=platform-windows
2. After you have that setup working, can test with some custom package. Ex: r-release: lazyeval_0.2.2.zip See example code below.

   
   DROP EXTERNAL LANGUAGE [myR]
   CREATE EXTERNAL LANGUAGE [myR]
   FROM (CONTENT = N'<pathtoextension>\R-lang-extension-windows-release.zip', 
   FILE_NAME = 'libRExtension.dll',
   ENVIRONMENT_VARIABLES = N'{"R_HOME": "C:\\Program Files\\R\\R-4.1.1"}');
   GO

sp_configure 'external scripts enabled', 1; RECONFIGURE WITH OVERRIDE;

CREATE EXTERNAL LIBRARY lazyeval FROM (CONTENT = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\lazyeval_0.2.2.zip') WITH (LANGUAGE = 'myR')

EXEC sp_execute_external_script @language =N'myR', @script=N'library(lazyeval)'

Let us know if it works for R extension.

> Also, I noticed that many of the examples show the CREATE EXTERNAL LIBRARY using a zip file for the content.. but the example for the regex library is just the DLL.. could this be the issue?

CREATE EXTERNAL LIBRARY using a .dll for the C# regex library is correct. (For r they use .zip and java they use .jar)

[kollerp]

I am having the same issue as the original poster. I tried registering lazyeval with R and that worked. Running it generated some R errors but it seems it at least ran the code in R. Any more ideas on this?

[RachelAmbler]

Also having the same issue. Has anyone managed to get this working as it stands...

[MartinBubenheimer]

Having the same issue. Did someone identify the root cause in the past 15 months and define a solution? I went through the steps provided by Garrett and the one difference that I could spot between his environment and mine is that I am not using SQL Server Express but SQL Server Developer. And I'm using R 4.2.2, not R 4.1.1. R 4.2.2 itself works on my machine and is usable, e.g. from RGui,

[Aniruddh25]

@digdugd, @kollerp, @RachelAmbler - we have new published newer version of C# extension https://github.com/microsoft/sql-server-language-extensions/releases/tag/dotnet-CSharp-v1.1.0, I wonder if you still face issues in installing the library.

@MartinBubenheimer we have a new published version of RExtension: https://github.com/microsoft/sql-server-language-extensions/releases/tag/R-v1.2.0 does your issue reproduce with this?