Register CLR function (WCF based) in SQL Server 2012

Question

I have a CLR based stored procedure which used MsmqIntegrationBinding to post messages to remote MSMQ's. Everything was working fine in SQL Server 2005 but now there is an upgrade from 2005 to 2012 suppose to happen. I tried to register the CLR (SP) in SQL Server 2012 but while registering System.ServiceModell.DLL it came up with the following error.

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'System.ServiceModel' failed because assembly 'microsoft.visualbasic.activities.compiler' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub.

I searched for the resolution and it appears that few other people are having the same problem and as of now there is no solution to it.

The primary reason for using MSMQIntegrationBinding is we wanted to make sure that each message is delivered only once. As you can see that I was pretty much interested in the ExactlyOnce property which is not there in the normal system.messaging class. We process thousand of messages and ordering of messages is very important further more each message is stamped with an id and if any message is sent which has an id that is less than the message previously sent the client system halts (big problem).

I also have rewritten the CLR stored proc using system.messaging. I just need suggestions whether it can support the scenario I mentioned above.

Answer 1

I have the same issue as you, and I don't know if there's a solution for it. What I have found is:

There are two types of .NET assemblies. Pure .NET assemblies only contain MSIL instructions. Mixed assemblies contain both unmanaged machine instructions and MSIL instructions. Mixed assemblies in general are compiled by C++ compiler with /clr switch but contain machine instructions resulting from native C++ code.

Regardless which version of SQL Server, CREATE ASSEMBLY only allows pure .NET assemblies to be registered. SQL Server has always required that an assembly to be loaded into SQL Server database with CREATE ASSEMBLY contains only MSIL instructions (pure assembly). CREATE ASSEMBLY will raise the above error if an assembly to be registered is mixed assembly.

From http://blogs.msdn.com/b/psssql/archive/2013/02/23/unable-to-register-net-framework-assembly-not-in-the-supported-list.aspx

The point is that we really need to refer to System.ServiceModel.

So, my guess is that, in the near future, there is no fix for it. What we have to do is code in another way.