CLR 程序集不会在 64 位 SQL Server 2005 中加载 [英] CLR assembly will not load in 64 bit SQL Server 2005

问题描述

我们在安装 SQL Server 2005(32 位)时使用了一个带有一些用户定义函数的程序集.我们使用这样的脚本将其部署到生产环境中:

We use an assembly with some user defined functions in our installation of SQL Server 2005 (32 bit). We deploy this to production with a script like this:

CREATE ASSEMBLY [Ourfunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A9000...000
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION [dbo].[GLOBAL_FormatString](@input [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Ourfunctions].[UserDefinedFunctions].[GLOBAL_FormatString]
GO

我们从未遇到过这些功能的任何问题.现在，当我们尝试将我们的一台服务器升级到 x64 时，我们在调用任何函数时都会出错.示例堆栈跟踪:

We have never experienced any problems with these functions. Now, when we tried to upgrade one of our servers to x64, we got errors when calling any of the functions. Sample stack trace:

System.Data.SqlClient.SqlException:一个Microsoft .NET 中发生错误尝试加载时的框架程序集 ID 65549.服务器可能是资源耗尽，或程序集可能不受信任PERMISSION_SET = EXTERNAL_ACCESS 或不安全.再次运行查询，或检查文档以查看如何解决组装信任问题.更多有关此错误的信息:System.IO.FileLoadException: 无法加载文件或程序集'我们的功能，版本=0.0.0.0，文化=中性，PublicKeyToken=null' 或其之一依赖关系.给定的程序集名称或代码库无效.(例外来自 HRESULT: 0x80131047)System.IO.FileLoadException: 在System.Reflection.Assembly.nLoad(AssemblyName文件名、字符串代码库、证据装配安全，装配locationHint、StackCrawlMark&堆栈标记，布尔值throwOnFileNotFound, Boolean -snip-

System.Data.SqlClient.SqlException: An error occurred in the Microsoft .NET Framework while trying to load assembly id 65549. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'ourfunctions, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047) System.IO.FileLoadException: at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean -snip-

错误提到权限集EXTERNAL_ACCESS AND UNSAFE 而我们使用级别SAFE.

The error mentions the permission sets EXTERNAL_ACCESS AND UNSAFE whereas we use the level SAFE.

.dll 文件是在目标平台设置为任何 CPU"的情况下构建的，当我们尝试从文件而不是 varbinary 语法加载 dll 时，我们得到了相同的结果.我们已经尝试了 http://support.microsoft.com/kb/918040

The .dll file is build with target platform set to 'Any CPU' and we get the same results when we try to load the dll from file instead of the varbinary syntax. We already tried the suggestions in http://support.microsoft.com/kb/918040

我们在 32 位机器上尝试了完全相同的过程，一切正常.它必须是 x86 和 x64 之间的区别.有什么想法吗?

We have tried the exact same procedure on a 32 bit machine and everything just worked. It must be a difference between x86 and x64. Any ideas?

解决方案:我们终于找到了解决方案.事实证明，我们的程序集确实是一个 32 位编译的程序集.在 Visual Studio 中，我们使用了目标Any CPU"，但在检查底层 .csproj 时，我发现了以下代码段:

SOLUTION: We finally found the solution. It turns out that our assembly was indeed a 32 bit compiled one. In Visual Studio, we used the target "Any CPU", but on inspecting the underlying .csproj, I found the following snippet:

  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    ...other elements...
    <PlatformTarget>x86</PlatformTarget>
  </PropertyGroup>

所以我们的Any CPU"目标实际上是构建一个 x86 程序集！啊啊啊我已经在 subversion 中追溯了这一行，但它在 2006 年第一次签入时就已经存在了.也许这是数据库项目的某个早期模板中的错误?

So our "Any CPU" target was actually building an x86 assembly! Aaargh. I have traced back this line in subversion, but it was already there at first checkin in 2006. Maybe this was a bug in some early template of the database project?

无论如何，谢谢你的帮助.我会接受 Russ 的回答，因为我怀疑许多遇到相同问题的人都会从他的回答中得到最大的帮助.

Anyway, thanks for your help. I will accept Russ's answer, as I suspect that many who experience the same problems will be helped most by his answer.

推荐答案

这与它是 64 位的事实无关，您需要更改 DB 以允许它.试试这个:

It doesn't have to do with the fact that it is 64 bit, you need to alter the DB to allow it. Try this:

ALTER DATABASE YOURDATABASEHERE
SET TRUSTWORTHY ON;
GO

如果单独这样做不起作用，您也可以尝试这些选项

if that alone doesn't work, you can try these options as well

USE YOURDATABASEHERE
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO 

这篇关于CLR 程序集不会在 64 位 SQL Server 2005 中加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！