A .NET Framework error occurred during execution of user-defined routine or aggregat

Question

I have a database will all CLR functions, I deploy it to a master database and then the rest of the servers get a backup and restore copy of it. For some reason some functionality works in some servers and not in other ones. I am aware the CLR routines requires external access and that is ok here. The assemblies below had to be manually deployed to make the original database work since I could not figure out how to have them deployed from VS2010 project.

Can somebody guide me on how to make the same CLR database function the same accross multiple servers (most of them are Windows 2008 64 bit, couple test machines are Windows 7 64 bit and we have few Windows 7 32 bit)

Thanks in advance.

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'System.DirectoryServices' and is_user_defined = 1)

CREATE ASSEMBLY [System.DirectoryServices] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'

WITH PERMISSION_SET = UNSAFE

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'System.Management' and is_user_defined = 1)

CREATE ASSEMBLY [System.Management] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Microsoft.Data.Schema.ScriptDom' and is_user_defined = 1)

CREATE ASSEMBLY [Microsoft.Data.Schema.ScriptDom] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Microsoft.Data.Schema.ScriptDom.dll'

WITH PERMISSION_SET = UNSAFE

IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'Microsoft.Data.Schema.ScriptDom.Sql' and is_user_defined = 1)

CREATE ASSEMBLY [Microsoft.Data.Schema.ScriptDom.Sql] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Microsoft.Data.Schema.ScriptDom.Sql.dll'

WITH PERMISSION_SET = UNSAFE

Msg 6522, Level 16, State 2, Line 1

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

System.IO.FileLoadException: Could not load file or assembly 'System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

System.IO.FileLoadException: 

   at UtilClr.FileSystemFunctions.GetVolumeInfo()

.

---

Gokhan Varol

Answer 1

Sounds like it's related to this behavior http://www.sqlskills.com/BLOGS/BOBB/post/Installing-NET-35-on-a-SQL-Server-2005-machinebe-careful.aspx. When using UNSAFE assemblies that are also in the GAC, the version that you compiled your assembly with must match the version in the GAC.

Cheers, Bob

Answer 2

Correct me if I am wrong but I checked the GAC and sys.assemblies and error report, they all seem like they have the same version. Why is the error still reported do you think?

SELECT * FROM sys.assemblies shows

name principal_id assembly_id clr_name permission_set permission_set_desc is_visible create_date modify_date is_user_defined

System.Management 1 65562 system.management, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil 3 UNSAFE_ACCESS 1 2011-08-16 13:29:02.980 2011-08-16 13:29:02.980 1

The error reports

Msg 6522, Level 16, State 2, Line 2

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

System.IO.FileLoadException: Could not load file or assembly 'System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

System.IO.FileLoadException: 

   at UtilClr.FileSystemFunctions.GetVolumeInfo()

C:\>"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\gacutil.exe" /l System.Management

Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.30729.1

Copyright (c) Microsoft Corporation.  All rights reserved.

The Global Assembly Cache contains the following assemblies:

  System.Management, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL

Number of items = 1

---

Gokhan Varol

Answer 3

Click on properties or use reflector to get the REAL 4-part version numbers. As an example, although the GAC column reports "2.0.0.0" for the version of System.Management.dll on my machine, right-clicking on the entry, choosing "Properties", and choosing the "Version" tab reports version 2.0.50727.5420.

In general (now watch, someone will find an exception ;-), if the third version number for a 2.0 assembly isn't 50727, you're looking at the wrong info.

Cheers, Bob

Answer 4

I used alter assembly to all my assemblies and it worked :-)

Thank you

 

ALTER ASSEMBLY [System.DirectoryServices] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll'

WITH PERMISSION_SET = UNSAFE

ALTER ASSEMBLY [System.Management] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

GO

ALTER ASSEMBLY [Microsoft.Data.Schema.ScriptDom] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Microsoft.Data.Schema.ScriptDom.dll'

WITH PERMISSION_SET = UNSAFE

GO

ALTER ASSEMBLY [Microsoft.Data.Schema.ScriptDom.Sql] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\Microsoft.Data.Schema.ScriptDom.Sql.dll'

WITH PERMISSION_SET = UNSAFE

GO

---

Gokhan Varol

Answer 5

To anyone else out there that might run into this issue, the company that I work for just installed .NET Framework 3.5 and had a similar issue.  After a server reboot, our Great Plains credit card encryption software stopped working and users would receive the "A .NET Framework error occurred during execution of user-defined routine or aggregate" error.

Simply running the following alter assembly script on the offending .dll seems to have resolved the issue.  Thanks to all those who contributed!

ALTER ASSEMBLY [System.Runtime.Remoting] FROM
'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Runtime.Remoting.dll'
WITH PERMISSION_SET = UNSAFE