无法加载文件或程序集“System.Data.DataSetExtensions"

2021-11-24 00:00:00 sql-server sqlclr .net-assembly

这是我第一次使用 SQL Server 2012，我在访问数据库时遇到了问题.我从 SQL Server 2008 R2 数据库进行了备份和还原，设置了登录名和权限，将数据库设置为可信任并启用了 clr.

我正在使用 Web 应用程序(IIS、ASP.Net)连接到数据库.当我将 Web 服务器指向新数据库时，出现以下错误.当我将它指向 SQL Server 2008 R2 机器上的数据库副本时，它会连接.

我将应用程序作为 2.0/3.5 版本运行，并试图确保所有内容都指向该框架.我已经验证 dll 位于两个数据库服务器上的相同文件夹中.

错误信息:

<块引用>

在执行用户定义的例程或聚合clrSP_UserData_PermList"期间发生 .NET Framework 错误:
System.IO.FileNotFoundException: 无法加载文件或程序集System.Data.DataSetExtensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"或其依赖项之一.该系统找不到指定的文件.

System.IO.FileNotFoundException: 无法加载文件或程序集System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"或其依赖项之一.系统找不到指定的文件.

System.IO.FileNotFoundException:

在 Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializerContract.CanSerialize(Type type)
在 System.Xml.Serialization.TempAssembly.LoadGeneratedAssembly(Type type, String defaultNamespace, XmlSerializerImplementation& contract)
在 System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)
在 Testdb.SqlServer.SqlUtility.XmlSerialize(Object oSerializableObject)
在 Testdb.SqlServer.StoredProcedures.clrSP_UserData_ PermList (Guid gUserID, SqlChars& sqlCharList)

我在互联网上搜索了好几天，但找不到适用于该数据库的解决方案(已启用 clr、安装了正确的框架等).

解决方案

鉴于 System.Data.DataSetExtensions 不在支持的 .NET 框架库，我将假设您通过 CREATE ASSEMBLY<手动将其加载到 SQL Server 中/code>(因此您必须启用 TRUSTWORTHY).自从您恢复了数据库后，其中的所有程序集都会出现(大多数情况下这是一件好事).但是，在这种特殊情况下，它并不是很有帮助.问题是 SQL Server 的 CLR 主机受到高度限制，并且链接到特定的 CLR 版本.SQL Server 2005、2008 和 2008 R2 链接到 CLR 2.0 及其相关的 .NET Framework 版本(2.0、3.0 和 3.5)，而 SQL Server 2012 和更新版本仅链接到 CLR 4.0 及其相关的 .NET Framework 版本(4.0 及更新版本).因为您从 2008 R2 开始，您手动加载了 System.Data.DataSetExtensions 的 .NET 3.5 版本.但是现在数据库在 SQL Server 2012 中运行，CLR 4.0 不会加载为 CLR 2.0/Framework 3.5 版编译的库.您需要将数据库中现有的 System.Data.DataSetExtensions 替换为 4.0 版本.

我认为这行不通，因为 System.Data.DataSetExtensions 是链接到您的程序集的依赖项，但您可以尝试删除它并重新添加它.p>
如果删除和重新创建不起作用，您可以尝试对其执行 ALTER ASSEMBLY，这是对当前定义的就地替换.什么时候可以执行 ALTER ASSEMBLY 是有限制的，但我认为它没有任何 SQLCLR 公开方法的事实可能允许它实际工作.
如果前两个想法不起作用，那么您将需要删除您的程序集，然后是 System.Data.DataSetExtensions，然后重新创建 System.Data.DataSetExtensions 使用 4.0 版本，然后重新部署您的程序集.

在你完成这些之后，交叉手指、祈祷、献祭、揉一只幸运的兔子的脚，希望 a) System.Data.DataSetExtensions 没有变成混合模式的程序集 (在任何时间点，从 4.0 的初始版本到当前的 4.6.2 或任何最新的框架版本)，并且 b) 自它确实在那个 4.0 转换中从纯 MSIL 变为混合模式，因此从 SQL Server 2012 开始变得不可加载，因为 SQL Server 的 CLR 主机中的这些限制之一是只允许纯 MSIL 程序集.

This is my first time working with SQL Server 2012 and I'm having a problem getting access to the database. I did a backup and restore from a SQL Server 2008 R2 database, set the logins and permissions, set the database to trustworthy and enabled clr.



I'm using a web application (IIS, ASP.Net) to connect to the database. When I point the web server to the new database I get the following error. When I point it to a copy of the database on a SQL Server 2008 R2 machine, it connects.  

I’m running the application as 2.0/3.5 version and tried to make sure that everything is pointed to that framework. I've verified that the dll is in the same folders on both database servers.  

Error message:

  A .NET Framework error occurred during execution of user-defined routine or aggregate "clrSP_UserData_PermList":

  System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.DataSetExtensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified. 
  
  System.IO.FileNotFoundException: Could not load file or assembly 'System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.
  
  System.IO.FileNotFoundException:   
  
  at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializerContract.CanSerialize(Type type)

  at System.Xml.Serialization.TempAssembly.LoadGeneratedAssembly(Type type, String defaultNamespace, XmlSerializerImplementation& contract)

  at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)

  at Testdb.SqlServer.SqlUtility.XmlSerialize(Object oSerializableObject)

  at Testdb.SqlServer.StoredProcedures.clrSP_UserData_ PermList (Guid gUserID, SqlChars& sqlCharList)  
I’ve searched the internet for days and I can’t find a solution that works for this database (clr is enabled, correct framework is installed, etc..).
 解决方案 Given that System.Data.DataSetExtensions is not in the list of Supported .NET Framework Libraries, I'm going to assume that you manually loaded it into SQL Server via CREATE ASSEMBLY (and hence why you had to enable TRUSTWORTHY). Since you restored the Database, all of the Assemblies in it came along for the ride (which is a good thing, most of the time). BUT, in this particular case it is not so helpful. The issue is that SQL Server's CLR host is highly restricted and is linked to a specific CLR version. SQL Server 2005, 2008, and 2008 R2 are linked to CLR 2.0 and its related .NET Framework versions (2.0, 3.0, and 3.5), while SQL Server 2012 and newer is linked to only CLR 4.0 and its related .NET Framework versions (4.0 and newer). Because you started on 2008 R2, you manually loaded the .NET 3.5 version of System.Data.DataSetExtensions. But now that the Database is running within SQL Server 2012, CLR 4.0 isn't going to load a library compiled for CLR 2.0 / Framework version 3.5. You will need to replace the existing System.Data.DataSetExtensions in your database with the 4.0 version.

I don't think this will work due to System.Data.DataSetExtensions being a dependency linked to your Assembly, but you could try dropping it and re-adding it.
If dropping and recreating doesn't work, you might could try doing an ALTER ASSEMBLY on it, which is an in-place replacement of the current definition. There are restrictions on when you can do an ALTER ASSEMBLY, but I think the fact that it doesn't have any SQLCLR exposed methods might allow this to actually work.
If those first two ideas don't work, then you will need to drop your Assembly, then System.Data.DataSetExtensions, and then recreate System.Data.DataSetExtensions with the 4.0 version, and then re-deploy your Assembly.
After you manage that, cross your fingers, pray, make offerings, rub a lucky rabbit's foot, and hopefully a) System.Data.DataSetExtensions did not get changed into a mixed-mode Assembly (at any point in time, from the initial release of 4.0 up through the current 4.6.2 or whatever the most recent Framework version is), and b) you are not also using System.ServiceModel since it did change from pure MSIL into mixed-mode in that 4.0 transition, and thus became unloadable starting in SQL Server 2012 given that one of those restrictions in SQL Server's CLR host is that only pure-MSIL assemblies are allowed.


	
		相关文章