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_external_script 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?

[gyssels]

Hi, I just spent 2 days on that issue: Here are my findings: (SOLVED FOR ME NOW)

the instruction to dowload the file and have the necessary rights on this document is correct:
https://learn.microsoft.com/en-us/sql/language-extensions/install/windows-c-sharp?view=sql-server-ver16

BUT the downloaded package that is found at https://github.com/microsoft/sql-server-language-extensions/releases/tag/dotnet-CSharp-v1.1.0
doesn't have the file as specified in the first link: dotnetextension.dll
So the next command executes with success and the execution of "EXEC sp_execute_external_script @language = N'DotNet'...." gives us an error

CREATE EXTERNAL LANGUAGE [DotNet]
FROM (CONTENT = N'C:\path\to\dotnet-core-CSharp-lang-extension-windows-release.zip',
FILE_NAME = 'dotnetextension.dll');
GO

https://learn.microsoft.com/en-us/sql/language-extensions/install/windows-c-sharp?view=sql-server-ver16
The page should be updated to nativecsharpextension.dll as found inside the zip file.

CREATE EXTERNAL LANGUAGE [DotNet]
FROM (CONTENT = N'C:\GYSSELS\DATA\SQL Server\MSSQL16.MSSQLSERVER\dotnet-core-CSharp-lang-extension-windows-release.zip',
FILE_NAME = 'nativecsharpextension.dll');
GO

THE EXECUTION OF SAMPLE REGEX went ok after that (after services restarts)

[gyssels]

For the R issue, that I solved earlier this week on my local SQL Server 2022 Developer edition,

--Very important: give the permissions to "NT Service\MSSQLLaunchpad" on "C:\Program Files\R\R-4.2.3\library" otherwise, while installing packages without the necessary rights, R will create another folder on the user folder where the SQL Server doesn't have access: "C:\Users...username...\AppData\Local\R\win-library\4.2

I THINK THAT PERMISSIONS STEP SHOULD BE POSTED ON THE R EXTERNAL LANGUAGE INSTALL DOCS

-After that, all was ok (After adding my user to folder rights, I also manually added the packages using the RGUi of the 4.2 version (use the one for SQL Server, but this step is not necessary, you should follow the docs)