I am new to SQL Server Language Extensions framework development and therefore, ran into some issues which I couldn't resolve by myself. More specifically, I want to use an external library (written in C# on .Net 6.0 platform) and invoke some methods to get string results and pass this result back to SQL Server calling procedure with sqlParams output parameter (SqlDataType varchar(max)). The library method works fine when invoked by console app but fails with the following error messages when invoked from within AbstractSqlServerExtensionExecutor-derived class by running sp_execute_external_script from SQL Server instance. External Language and External Library both are installed correctly and worked properly when I tested with a simple input/output dataset but as soon as I tried to link my DataIntegrationExecutor class (derived as I already mentioned above from AbstractSqlServerExtensionExecutor) I get errors. Below I give a fragment of my code (which I think causes error when initializing type but I can't understand a reason for the error). So, here is a code fragment:
Msg 39004, Level 16, State 20, Line 3
A 'CSharp' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
STDOUT message(s) from external script:
nativecsharpextension::Init
DotnetEnvironment::to_utf16_str
DotnetEnvironment::Init
DotnetEnvironment::load_hostfxr
DotnetEnvironment::load_library
DotnetEnvironment::get_export
DotnetEnvironment::get_export
DotnetEnvironment::get_export
DotnetEnvironment::get_dotnet
DotnetEnvironment::get_dotnet_load_assembly
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::Init
nativecsharpextension::InitSession
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::InitSession
CSharpSession::CSharpSession
nativecsharpextension::InitColumn
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::InitColumn
CSharpSession::InitInputColumn
CSharpInputDataSet::InitColumn
nativecsharpextension::InitColumn
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::InitColumn
CSharpSession::InitInputColumn
CSharpInputDataSet::InitColumn
nativecsharpextension::InitParam
DotnetEnvironment::to_utf16_s
STDOUT message(s) from external script:
tr
DotnetEnvironment::to_utf16_str
CSharpExtension::InitParam
CSharpSession::InitParam
CSharpParamContainer::AddParam
nativecsharpextension::Execute
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::Execute
CSharpSession::Execute
CSharpInputDataSet::AddColumns
CSharpInputDataSet::AddColumn
CSharpInputDataSet::AddColumn
CSharpUserDll::InstantiateUserExecutor
at VendorProductsParserLib.Parser.ParseVendorProducts(Vendors vendor, ProductTypes productType) in C:\Users\Zviad\source\repos\SqlCSharpLanguageExtensions\VendorProductsParserLib\Parser.cs:line 468
at DataIntegrationLib.DataIntegrationExecutor.Execute(DataFrame input, Dictionary`2 sqlParams) in C:\Users\Zviad\source\repos\SqlCSharpLanguageExtensions\DataIntegrationLib\DataIntegration.cs:line 24
at Microsoft.SqlServer.CSharpExtension.CSharpSession.Execute(UInt64 rowsNumber, Void data, Int32 strLenOrNullMap, UInt16* outputSchemaColumnsNumber) in C:__w\1\s\sql-server-language-extensions\language-ext
STDOUT message(s) from external script:
ensions\dotnet-core-CSharp\src\managed\CSharpSession.cs:line 189
at Microsoft.SqlServer.CSharpExtension.CSharpExtension.<>c__DisplayClass14_0.b0() in C:\w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpExtension.cs:line 432
at Microsoft.SqlServer.CSharpExtension.ExceptionUtils.WrapError(Action func) in C:__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\ExceptionUtils.cs:line 35Error: The type initializer for 'VendorProductsParserLib.Parser' threw an exception.
nativecsharpextension::CleanupSession
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::CleanupSession
CSharpSession::CleanupSession
CSharpParamContainer:HandleCleanup
CSharpOutputDataSet::HandleCleanup
nativecsharpextension::Cleanup
Is using of System.Data.SqlClient 4.8.5 package allowed in libraries which are loaded by External Library installed as a CSharp Language extension on SQL Server 2019 CU21 instance?
I would also be so grateful if you could instruct how to debug this library from within Visual Studio 2022 because starting debugging SQL Script from Visual Studio (which I used to use for debugging CLR stored procedures) didn't have an expected result i.e. debugging didn't work.
Hello,
I am new to SQL Server Language Extensions framework development and therefore, ran into some issues which I couldn't resolve by myself. More specifically, I want to use an external library (written in C# on .Net 6.0 platform) and invoke some methods to get string results and pass this result back to SQL Server calling procedure with sqlParams output parameter (SqlDataType varchar(max)). The library method works fine when invoked by console app but fails with the following error messages when invoked from within AbstractSqlServerExtensionExecutor-derived class by running sp_execute_external_script from SQL Server instance. External Language and External Library both are installed correctly and worked properly when I tested with a simple input/output dataset but as soon as I tried to link my DataIntegrationExecutor class (derived as I already mentioned above from AbstractSqlServerExtensionExecutor) I get errors. Below I give a fragment of my code (which I think causes error when initializing type but I can't understand a reason for the error). So, here is a code fragment:
static Parser() { httpClient = new HttpClient(); connectionString = $"Server={Environment.MachineName};Initial Catalog={Settings.Default.InitialCatalog};Integrated Security=True"; connection = new SqlConnection(connectionString); errorLoggingCommand = connection.CreateCommand(); errorLoggingCommand.CommandType = CommandType.StoredProcedure; errorLoggingCommand.CommandText = "dbo.uspErrorLogging"; connection.Open(); }
Msg 39004, Level 16, State 20, Line 3 A 'CSharp' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. STDOUT message(s) from external script: nativecsharpextension::Init DotnetEnvironment::to_utf16_str DotnetEnvironment::Init DotnetEnvironment::load_hostfxr DotnetEnvironment::load_library DotnetEnvironment::get_export DotnetEnvironment::get_export DotnetEnvironment::get_export DotnetEnvironment::get_dotnet DotnetEnvironment::get_dotnet_load_assembly DotnetEnvironment::to_utf16_str DotnetEnvironment::to_utf16_str CSharpExtension::Init nativecsharpextension::InitSession DotnetEnvironment::to_utf16_str DotnetEnvironment::to_utf16_str CSharpExtension::InitSession CSharpSession::CSharpSession nativecsharpextension::InitColumn DotnetEnvironment::to_utf16_str DotnetEnvironment::to_utf16_str CSharpExtension::InitColumn CSharpSession::InitInputColumn CSharpInputDataSet::InitColumn nativecsharpextension::InitColumn DotnetEnvironment::to_utf16_str DotnetEnvironment::to_utf16_str CSharpExtension::InitColumn CSharpSession::InitInputColumn CSharpInputDataSet::InitColumn nativecsharpextension::InitParam DotnetEnvironment::to_utf16_s STDOUT message(s) from external script: tr DotnetEnvironment::to_utf16_str CSharpExtension::InitParam CSharpSession::InitParam CSharpParamContainer::AddParam nativecsharpextension::Execute DotnetEnvironment::to_utf16_str DotnetEnvironment::to_utf16_str CSharpExtension::Execute CSharpSession::Execute CSharpInputDataSet::AddColumns CSharpInputDataSet::AddColumn CSharpInputDataSet::AddColumn CSharpUserDll::InstantiateUserExecutor at VendorProductsParserLib.Parser.ParseVendorProducts(Vendors vendor, ProductTypes productType) in C:\Users\Zviad\source\repos\SqlCSharpLanguageExtensions\VendorProductsParserLib\Parser.cs:line 468 at DataIntegrationLib.DataIntegrationExecutor.Execute(DataFrame input, Dictionary`2 sqlParams) in C:\Users\Zviad\source\repos\SqlCSharpLanguageExtensions\DataIntegrationLib\DataIntegration.cs:line 24 at Microsoft.SqlServer.CSharpExtension.CSharpSession.Execute(UInt64 rowsNumber, Void data, Int32 strLenOrNullMap, UInt16* outputSchemaColumnsNumber) in C:__w\1\s\sql-server-language-extensions\language-ext STDOUT message(s) from external script: ensions\dotnet-core-CSharp\src\managed\CSharpSession.cs:line 189 at Microsoft.SqlServer.CSharpExtension.CSharpExtension.<>c__DisplayClass14_0.b0() in C:\w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpExtension.cs:line 432
at Microsoft.SqlServer.CSharpExtension.ExceptionUtils.WrapError(Action func) in C:__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\ExceptionUtils.cs:line 35Error: The type initializer for 'VendorProductsParserLib.Parser' threw an exception.
nativecsharpextension::CleanupSession
DotnetEnvironment::to_utf16_str
DotnetEnvironment::to_utf16_str
CSharpExtension::CleanupSession
CSharpSession::CleanupSession
CSharpParamContainer:HandleCleanup
CSharpOutputDataSet::HandleCleanup
nativecsharpextension::Cleanup
Is using of System.Data.SqlClient 4.8.5 package allowed in libraries which are loaded by External Library installed as a CSharp Language extension on SQL Server 2019 CU21 instance?
I would also be so grateful if you could instruct how to debug this library from within Visual Studio 2022 because starting debugging SQL Script from Visual Studio (which I used to use for debugging CLR stored procedures) didn't have an expected result i.e. debugging didn't work.
Regards,
Zviad Gogiashvili