After updating Entity Framework my CLR StoredProcedure stopped working until I compiled it again

Question

I ask this question before sql server clr-how to call wcf service in clr sql stored procedure in visual studio and create a stored procedure to call wcf service but when I update entity framework 4.5 to 4.5.1 on my server stored procedure not work and its error around of System.ServiceModel assembly
I forced to do all step in answer of my old question and publish stored procedure again to work.

but I confused, because my sql server is 2008 r2 and it work with entity framework 3.5, why I must republish my stored procedure and all steps Can any one help me?

Answer 1

I think your way to solve problem is not correct,
For Call WCF Service in SQL Server you must use System.Web.Service instead of System.ServiceModule
I you use this assembly you do not force to add

• System.Messaging
• System.IdentityModel
• System.IdentityModel.Selectors
• Microsoft.Transactions.Bridge
• System.Runtime.Serialization
• System.ServiceModel

this way is very simple, just add service like old web service
for more information use Calling WCF in CLR

Answer 2

I had a related error some time ago. The thrown error was:

Could not load type 'System.ServiceModel.Activation.HttpModule' from assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

This error can occurrs when IIS is installed/updated after .NET Framework 4 has been installed, or whether the module Http Activation v3.0 is installed after .NET Framework 4 has been installed. Whatever, the problem as you mentioned, was the update of the EF.

Maybe this can be helpful:

• Run the tool ASP.NET IIS Registration Tool (Aspnet_regiis.exe)
  
• Run the command to register the new version:
  C:\Windows\Microsoft.NET\Framework64\v4.0.30319>aspnet_regiis.exe –iru

You can get more info here:

• ASP.NET IIS Registration Tool (Aspnet_regiis.exe)

Or maybe you want to update a library in the GAC. The libraries of the framework are cached in the GAC, check this article How to: Install an Assembly into the Global Assembly Cache and this topic Updating a DLL in the GAC

Answer 3

The reason you got the error on System.ServiceModel is because:

• You loaded System.ServiceModel.dll into SQL Server as it is not a supported .Net Framework library

• You updated the main System.ServiceModel.dll, probably as part of upgrading Entity Framework

• The version of the DLL loaded into SQL Server no longer matched the version of the DLL in the GAC

• The version of the DLL loaded into SQL Server must match the version of the DLL in the GAC:

  If you have unsupported libraries registered in a SQL Server database, your SQL Server application may stop working ... because servicing or upgrading libraries in the GAC does not update assemblies inside SQL Server. If an assembly exists both in a SQL Server database and in the GAC, the two copies of the assembly must exactly match. If they do not match, an error will occur when the assembly is used by SQL Server CLR integration.

  ( Breaking Changes in SQL Server 2008/SQL Server 2008R2 )

---

And I think I just found the exact error that you ran into. Check out this page:

Error message when you execute a CLR routine or use an assembly in SQL Server: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)"

Which states (in "Scenario 1"):

You create a common language runtime (CLR) routine that references a Microsoft .NET Framework assembly. The .NET Framework assembly is not documented in Knowledge Base article 922672. Then, you install the .NET Framework 3.5 or a .NET Framework 2.0-based hotfix.

That scenario supports what I had mentioned above the line.