无法加载文件或程序集'System.Data.DataSetExtensions [英] Could not load file or assembly 'System.Data.DataSetExtensions
问题描述
这是我第一次使用SQL Server 2012，但在访问数据库时遇到问题。我从SQL Server 2008 R2数据库进行了备份和还原，设置了登录名和权限，将数据库设置为可信任并启用了clr。
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.
我正在使用Web应用程序（IIS，ASP.Net）连接到数据库。当我将Web服务器指向新数据库时，出现以下错误。当我将其指向SQL Server 2008 R2计算机上的数据库副本时，它会连接。
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.
我正在以2.0 / 3.5版本运行该应用程序，并尝试确保所有内容都指向该框架。我已确认该dll位于两台数据库服务器上的同一文件夹中。
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.
错误消息：
在执行用户期间发生.NET Framework错误定义的例程或聚合 clrSP_UserData_PermList：
System.IO.FileNotFoundException：无法加载文件或程序集'System.Data.DataSetExtensions，版本= 4.0.0.0，区域性=中性，PublicKeyToken = b77a5c561934e089'或它的依赖项之一。该系统找不到指定的文件。
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：无法加载文件或程序集'System.Data.DataSetExtensions，Version = 3.5.0.0，Culture = neutral，PublicKeyToken = b77a5c561934e089'或一个它的依赖关系。系统找不到指定的文件。
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：
System.IO.FileNotFoundException:
在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（类型类型，字符串defaultNamespace）在Testdb.SqlServer.SqlUtility.XmlSerialize（Object oSerializableObject）中的
在Testdb.SqlServer.StoredProcedures.clrSP_UserData_ PermList（Guid gUserID，SqlChars& sqlCharList）
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)
我已经在互联网上搜索了几天，但找不到适用于该数据库的解决方案（启用了clr，安装了正确的框架，等等。）。
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..).
推荐答案
给出 System.Data.DataSetExtensions 不在受支持的.NET Framework库，我假设您是通过<$ c $手动将其加载到SQL Server中的c>创建组装（以及为什么必须启用 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版编译的库。您将需要使用4.0版本替换数据库中现有的 System.Data.DataSetExtensions 。
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.
-
由于 System.Data.DataSetExtensions 是与您的Assembly关联的依赖项，因此我认为这行不通，但是您可以尝试删除它并重新添加它。
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.
如果删除和重新创建不起作用，则可以尝试在其上执行 ALTER ASSEMBLY ，替换当前定义。何时可以执行 ALTER ASSEMBLY 有一些限制，但是我认为它没有任何SQLCLR公开方法的事实可能会使它真正起作用。
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.
如果前两个想法不起作用，则需要删除Assembly，然后是 System.Data.DataSetExtensions ，然后用4.0版本重新创建 System.Data.DataSetExtensions ，然后重新部署您的程序集。
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.
管理好之后，用手指交叉，祈祷，供奉，擦掉幸运的兔子脚，并希望a） System.Data.DataSetExtensions 未更改为混合模式Assembly（在任何时候，从4.0的初始发行版到当前的4.6.2或最新的Framework版本，以及b）自以来，您也没有使用 System.ServiceModel >在该4.0过渡中，它确实从纯MSIL变为混合模式，因此成为卸载对象从SQL Server 2012开始，因为SQL Server CLR主机的限制之一是仅允许纯MSIL程序集。
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.
这篇关于无法加载文件或程序集'System.Data.DataSetExtensions的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
