search

microsoft / sql-server-language-extensions

language-extensions-sqlserver
Other
106 stars 42 forks source link

C# regex sample "Could not load file or assembly" #42

Closed jfabry closed 6 months ago

jfabry commented 11 months ago

Hi all,

I am attempting to get the sample for .Net C# working, but I get the following error in the EXEC sp_execute_external_script step Error: Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\ExternalLibraries\5\65537\1\regex'. The specified module could not be found. (Full error message below).

The file exists and is indeed the dll of the regex sample that I built and installed successfully. Suspecting permissions issues, I checked permissions on the directory and icacls tells me: ExternalLibraries APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(RX). I have also tried with APPLICATION PACKAGE AUTHORITY\ALL APPLICATION PACKAGES:(I)(OI)(CI)(F) but to no avail.

I am using the .NET C# language extension v1.1.0 on a fresh install of Microsoft SQL Server Developer (64-bit) v 16.0.1000.6, on a Windows 10 Pro (10.0) machine.

I am not sure on how to proceed at this point. Any help would be greatly appreciated.

Thanks in advance!

Msg 39004, Level 16, State 20, Line 1
A 'Dotnet' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. External script request id is 471D2C07-9D10-47AC-A47F-F805057E320F.
STDOUT message(s) from external script: 
   at System.Runtime.Loader.AssemblyLoadContext.<LoadFromPath>g____PInvoke|5_0(IntPtr ptrNativeAssemblyBinder, UInt16* ilPath, UInt16* niPath, ObjectHandleOnStack retAssembly)
   at System.Runtime.Loader.AssemblyLoadContext.LoadFromAssemblyPath(String assemblyPath)
   at System.Reflection.Assembly.LoadFrom(String assemblyFile)
   at Microsoft.SqlServer.CSharpExtension.DllUtils.GetUserDll(String userClassName, List`1 dllList) in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\DllUtils.cs:line 45Error: Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\ExternalLibraries\5\65537\1\regex'. The specified module could not be found.
   at Microsoft.SqlServer.CSharpExtension.DllUtils.GetUserDll(String userClassName, List`1 dllList) in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\utils\DllUtils.cs:line 62
   at Microsoft.SqlServer.CSharpExtension.CSharpUserDll.Instantiat
STDOUT message(s) from external script: 
eUserExecutor() in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpUserDll.cs:line 84
   at Microsoft.SqlServer.CSharpExtension.CSharpSession.Execute(UInt64 rowsNumber, Void** data, Int32** strLenOrNullMap, UInt16* outputSchemaColumnsNumber) in C:\__w\1\s\sql-server-language-extensions\language-extensions\dotnet-core-CSharp\src\managed\CSharpSession.cs:line 185
   at Microsoft.SqlServer.CSharpExtension.CSharpExtension.<>c__DisplayClass14_0.<Execute>b__0() 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: Unable to find user class with full name: UserExecutor.CSharpRegexExecutor
Please provide user class in the form of LibraryName;Namespace.Classname or Namespace.Classname
jfabry commented 11 months ago

I have found the underlying problem and produced a workaround, and am leaving it here at least for reference.

The issue is that Assembly.LoadFrom(string assemblyFile) expects the path assemblyFile to have a filename extension. The file C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\ExternalLibraries\5\65537\1\regex does not. This then throws a FileNotFoundException, aborting the load.

The workaround that I'm using is as follows:

  1. add a .dll filename extension to the regex file,
  2. change the script argument of sp_execute_external_script to @script = N'regex.dll;UserExecutor.CSharpRegexExecutor' (i.e. adding .dll as well).

This makes the example work for me. I expect that this is a general issue, not just limited to this regex sample, so it seems that a more fundamental fix would be needed.

jfabry commented 6 months ago

Fixed by pull request #44