Search code examples
sql-serverclr.net-assemblysql-server-2017

Error during the registration of a CLR assembly in SQL Server 2017 which references other assembly

I got the following error when I try to deploy my DLL which references other ThirdPartyDLL.

Msg 10300, Level 16, State 2, Line 4
Assembly 'MYDLL' references assembly 'ThirdPartyDLL, version=300.1.0.1, culture=neutral, publickeytoken=5c915cbb2b8fbc32.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.

I used this statemant to deploy my dll:

USE MYDATABASE
GO

CREATE ASSEMBLY MYDLL
FROM 'C:\Program Files (x86)\MYDLL\MYDLL.dll'
WITH PERMISSION_SET = UNSAFE;
Go

I'm sure the ThirdPartyDLL is correctly installed but I get the error base on mismatching!!! For example result of this:

SELECT *
FROM sys.assemblies AS a
    INNER JOIN sys.assembly_files AS f ON a.assembly_id = f.assembly_id
FOR JSON PATH

is:

DLL ERROR

Notice:

I was able to register MYDLL on the following version of SQL Server:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) Jun 17 2011 00:57:23 Copyright (c) Microsoft Corporation Standard Edition on Windows NT 6.1 (Build 7601: Service Pack 1) (WOW64) (Hypervisor)

Solution

  • I found the crazy reason for this problem. Base on the following threads:

    SQL Server "version, culture or public key mismatch" during "create assembly" when loading XMLSerializers created with sgen utility

    And

    https://learn.microsoft.com/en-us/answers/questions/77832/i-got-the-following-error-when-i-try-to-deploy-my.html?sort=votes

    I was referencing an x86 assembly on a 64-bit server. SQL Server couldn't resolve the reference (based on mismatching architecture) and install the assembly. It means you could install any DLL (x86/x64) on SQL Server 2017 but just when there aren't any references. When there is a referenced dll, it has to pass SQL Server architecture, so it could find it.

    Notice:

    It's possible to change dll architecture from x64 (64 bit) to x86 (32 bit) using the MS CorFlags.exe.

    https://learn.microsoft.com/en-us/dotnet/framework/tools/corflags-exe-corflags-conversion-tool