Getting WCF To Work With SQL CLR Objects 64bit Environment

This past week I have been wrapped up in moving our integration services between Microsoft Dynamics AX and our line of business applications. If you have seen some of my past posts, it will show that I chose to use WCF and Sql Server Service Broker. One of the biggest challenges was getting my WCF/SQLCLR stored procedures to work in the new test environment which happens to be 64bit. In our dev environment I did not have any trouble going from my initial 32bit to 64bit, however it had been a couple of months since I had set it up and like most things, a lot of the details I forgot. Below is a list of assemblies that needed to be added into SQL Server, but only after careful testing of each combination(64/32). If you look closely you will notice that some reference dlls in both the 64bit and 32bit .Net Framework.

Steps:

 

1.       Enable CLR Functionality in SQL Server

EXEC sp_configure 'clr enabled', 1;RECONFIGURE WITH OVERRIDE;GO2.    ALTER DATABASE [Database Name] SET Trustworthy ON 3.              

 CREATE ASSEMBLY 

 [System.Web] from 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'

 with permission_set = UNSAFE –-Fails if not 64 on 64 bit machines

 GO 

 CREATE ASSEMBLY

 SMDiagnostics from 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication  Foundation\SMDiagnostics.dll'

 with permission_set = UNSAFE

 GO 

 CREATE ASSEMBLY 

 [System.Runtime.Serialization] from 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'

 with permission_set = UNSAFE 

 GO 

 CREATE ASSEMBLY  

 [System.IdentityModel] from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'

 with permission_set = UNSAFE

 GO
      CREATE ASSEMBLY        [System.IdentityModel.Selectors] from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0 \System.IdentityModel.Selectors.dll'      with permission_set = UNSAFE      GO            CREATE ASSEMBLY       [System.Messaging] from      'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Messaging.dll'      with permission_set = UNSAFE      GO            CREATE ASSEMBLY      [Microsoft.Transactions.Bridge] from      'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'      with permission_set = UNSAFE      GO

A couple of other gotcha's, I was also having an issue in my local environment where the Sql CLR was having trouble loading the Microsoft.VisualStudio.Diagnostics.ServiceModelSink.dll when running my managed SPs. I found a fix here which was not so bad, except that I could not change my machine.config file. I was simply trying to open it up in Notepad and Visual Studio, make the change and save it. This did not work so after a couple of minutes of stumbling I decided to re-create it, make the modifications, save it to another location and then cut and copy it to it's original location. This allowed Vista to handle permissions and everything worked out.

One last thing, if you try and make a service reference with SQLCLR Objects by default I think it targets the 2.0 framework. Of course if you are using WCF you need to target at least 3.0 so you can have the option for setting Service References.

Currently rated 3.1 by 14 people

  • Currently 3.071429/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Posted by: BayerWhite
Posted on: 2/27/2009 at 1:48 AM
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (106) | Post RSSRSS comment feed

Comments

Comments are closed