Microsoft Extensibility SDK for C# for SQL Server

Question

https://learn.microsoft.com/en-us/sql/language-extensions/tutorials/search-for-string-using-regular-expressions-in-c-sharp?view=sql-server-ver16

As mentioned in the above link carried out all the steps. However when I execute below command I am getting below error. Could you please help to resolve this issue.

==

error displayed is:

STDOUT message(s) from external script:

Failed to resolve full path of dotnet root [R:\Data\MSSQL15.MSSQLSERVER\MSSQL]

Error: Init failed: 0x0x80008085

Error: Get delegate failed: 0x0x80008081

Msg 39004, Level 16, State 20, Line 1

A 'dotnet' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.

STDOUT message(s) from external script:

Failed to resolve full path of dotnet root [R:\Data\MSSQL15.MSSQLSERVER\MSSQL]

Error: Init failed: 0x0x80008085

Error: Get delegate failed: 0x0x80008081

===

command executed in SQL server:

DECLARE @rowsCount INT;

DECLARE @regexExpr VARCHAR(200);

SET @regexExpr = N'[Cc]#';

EXEC sp_execute_external_script @language = N'dotnet',

@script = N'regex.dll;UserExecutor.CSharpRegexExecutor',

@input_data_1 = N'SELECT * FROM testdata',

@params = N'@regexExpr VARCHAR(200) OUTPUT, @rowsCount INT OUTPUT',

@regexExpr = @regexExpr OUTPUT,

@rowsCount = @rowsCount OUTPUT

WITH result sets((

        id INT,

        TEXT VARCHAR(100)

        ));

SELECT @rowsCount AS rowsCount, @regexExpr AS message;

===

Answer 1

Is .net core installed on the Sqlserver and the same version used to create the dll?

Answer 2

Hi, @Tarale, Sandeep. Welcome to Microsoft Q&A. 

After testing, the RegexSample.dll you provided could run normally.

 

Please integrate the RegexSample.dll into Sql Server as follows(Please check the contents of step 2 carefully):

1.First, please make sure that your Sql Server has Machine Learning Services and Language Extensions installed.

2.Create the external language dotnet. If you have already created regex.dll and dotnet, you could delete them with the following command.

DROP EXTERNAL LIBRARY [regex.dll]; 
DROP EXTERNAL LANGUAGE [dotnet]; 

Download dotnet-core-CSharp-lang-extension-windows-release.zip from link.

In Sql Server, switch the database to csharptest and create the external language dotnet.

CREATE EXTERNAL LANGUAGE [dotnet]

FROM (

    CONTENT = N'<path>\dotnet-core-CSharp-lang-extension.zip',

    FILE_NAME = 'nativecsharpextension.dll'

);

GO

 

Please note: CONTENT is the absolute address of dotnet-core-CSharp-lang-extension-windows-release.zip. FILE_NAME is the nativecsharpextension.dll file in dotnet-core-CSharp-lang-extension-windows-release.zip.

It is not dotnetextension.dll

3.Restart SQL Server Launchpad and database engine.

4.Use the following command to confirm whether the value of external scripts enabled is 1.

EXEC sp_configure;

If not, run the following command.

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

5.Set permissions as described in the document.

6.Switch to the csharptest database. Run the following command to integrate the RegexSample.dll you created.

CREATE EXTERNAL LIBRARY [regex.dll]
FROM (CONTENT = N'<path>\RegexSample.dll')
WITH (LANGUAGE = 'dotnet');
GO

Note: CONTENT is the absolute address of your RegexSample.dll. LANGUAGE is the dotnet we created in step 2.

7.According to the official document, execute the query.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.