SQL Server 自定义 CLR 失败并显示错误“无法加载文件或程序集或其依赖项之一.系统找不到指定的文件." [英] SQL Server custom CLR fails with error "Could not load file or assembly or one of its dependencies. The system cannot find the file specified."

问题描述

我创建了一个引用 Windows DLL WindowsBase 的 SQL CLR.在大多数情况下，我的 CLR 工作得很好，但是如果 WindowsBase 在 GAC 中得到更新，那么我会收到错误主机存储中的程序集与 GAC 中的程序集具有不同的签名.".

I have created a SQL CLR that references the Windows DLL, WindowsBase. For the most part my CLR works just fine but if WindowsBase gets updated in the GAC then I get the error "Assembly in host store has a different signature than assembly in GAC.".

为了解决这个问题，我构建了我的 CLR 以引用不在 GAC 中的 WindowsBase 版本.现在，当我运行 CLR 时，出现错误无法加载文件或程序集 'WindowsBase, Version=4.0.0.0, ...' 或其依赖项之一.系统找不到指定的文件."

To address this I built my CLR to reference a version of WindowsBase that is not in the GAC. Now when I run my CLR i get the error "Could not load file or assembly 'WindowsBase, Version=4.0.0.0, ...' or one of its dependencies. The system cannot find the file specified."

我设置了 FusLogVw 来查看发生了什么并得到以下输出

I set up FusLogVw to see what's going on and get the following output

The system cannot find the file specified.

Assembly manager loaded from:  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\clr.dll
Running under executable  C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
--- A detailed error log follows. 

=== Pre-bind state information ===
    LOG: DisplayName = WindowsBase, Version=4.0.0.0
    LOG: Appbase = file:///C:/Program Files/Microsoft SQL Server/MSSQL11.MSSQLSERVER/MSSQL/Binn/
    LOG: Initial PrivatePath = NULL
    LOG: Dynamic Base = NULL
    LOG: Cache Base = NULL
    LOG: AppName = sqlservr.exe
    Calling assembly : (Unknown).
    ===
    LOG: This bind starts in default load context.
    LOG: No application configuration file found.
    LOG: Using host configuration file: 
    LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config.
    LOG: Post-policy reference: WindowsBase, Version=4.0.0.0
    LOG: The same bind was seen before, and was failed with hr = 0x80070002.
    ERR: Unrecoverable error occurred during pre-download check (hr = 0x80070002).

看起来程序集加载器正在使用 machine.config 文件来确定它应该在哪里查找程序集.基于这个假设，我用

It would appear that the assembly loader is using the machine.config file to determine where it should look for the assembly. Based on this assumption I updated the machine.config with

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
        <dependentAssembly>
             <assemblyIdentity name="WindowsBase"... />
              <codBase version="4.0.0.0"
                   href="./AdditionalAssemblies/WindowsBase.dll"/>
        </dependentAssembly>
    </assemblyBinding>
</runtime>

我已尝试使用相对路径(相对于 SQlservr.exe)和绝对路径，但仍然出现上述相同的错误.关于如何设置 SQL CLR 以便它引用 GAC 之外的 WindowsBase 副本而不会出现上述错误的任何建议将不胜感激.

I have tried with a relative path (relative to SQlservr.exe) and an absolute path but continue to get the same error mentioned above. Any suggestions on how to set up the SQL CLR so that it refereneces a copy of WindowsBase that is outside the GAC without getting the above errors would be greatly appreciated.

~Bugz~

推荐答案

SQL Server 的 CLR 主机不支持您尝试执行的操作.SQL Server 中的 CLR 受到严格限制，以防止破坏 SQL Server 的稳定性，因为它的工作方式与在操作系统上运行的应用程序不同.因此，支持的 DLL 集非常有限(即经过验证可以工作并保证在更新到 .NET 后仍能正常工作).WindowsBase 不是其中之一，因此您需要将其作为 UNSAFE 手动加载到 SQL Server 中.但这会让您遇到主 GAC 中版本更改的问题(GAC 和 SQL Server 的 CLR 主机之间常见的 DLL 必须是相同版本)，或者更糟的是，如果 DLL 变得混合";(非托管 C++ 和托管代码)并且不再纯".在这种情况下，新版本将不会加载，而旧版本将获得错误版本".错误，所以你有一些工作要做.

What you are attempting to do is not supported by SQL Server's CLR host. The CLR within SQL Server is highly restricted to prevent destabilizing SQL Server since it works differently than apps running on the OS. So, there is a very limited set of DLLs that are supported (i.e. verified to work and guaranteed to stay working across updates to .NET). WindowsBase is not one of them so you would need to load it manually as UNSAFE into SQL Server. But that leaves you with either the problem that you ran into of the version in the main GAC changing (DLLs that are common between the GAC and SQL Server's CLR host must be the same version), or worse, if the DLL becomes "mixed" (both unmanaged C++ and managed code) and is no longer "pure". In that case the new version will not load and the old version gets the "wrong version" error so you have some work to do.

有关更多详细信息，请参阅以下文章/文档:

For more detailed information, please see the following articles / documentation:

• SQL Server 2005 支持的 .NET 框架库
• SQL Server 2008/2008 R2/2012/2014 支持的 .NET Framework 库
• 支持政策SQL Server CLR 托管环境中未经测试的 .NET Framework 程序集
• 错误当您在 SQL Server 中执行 CLR 例程或使用程序集时出现消息:宿主存储中的程序集与 GAC 中的程序集具有不同的签名.(来自 HRESULT 的异常:0x80131050)"

这组链接取自附加阅读"我写的文章的一部分:通往 SQLCLR 5 级的阶梯:开发(在 SQL Server 中使用 .NET).

That set of links is taken from the "Additional Reading" section of an article I wrote: Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server).

有关使用 SQLCLR 的更多信息，请访问我的网站:SQLCLR 信息

For more information on working with SQLCLR in general, please visit my site: SQLCLR Info

这篇关于SQL Server 自定义 CLR 失败并显示错误“无法加载文件或程序集或其依赖项之一.系统找不到指定的文件."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！