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

MSDN Roadshow Tiki Hut Tour - Winter 2009 In Jax Feb. 19, 2009

Currently the weather in Jax says, "57" however I think in the next couple of days it will be getting alot hotter! The Tiki Hut Tour is coming to Jacksonville, Fl. Febuary 19th from 6:30-9:30. What makes it so hot you ask? Just check out these topics...

Session 1 – jQuery with ASP.NET - JQuery is an open source JavaScript library that has a passionate following among Ajax developers. Microsoft is integrating the open source JQuery library into both the ASP.NET Web Forms and ASP.NET MVC frameworks and providing full product support. Learn how you can take advantage of JQuery to build richly interactive client-side Ajax applications when developing either ASP.NET Web Forms or ASP.NET MVC applications. Also see how JQuery works in combination with ASP.NET AJAX to provide the best framework for building Ajax applications.

Session 2 – Data Services - OnPremise and Off - In the near future, applications will be developed using a combination of custom application code and online building block services, including data-centric services. In this session we discuss advancements in the Microsoft development platform and online service interfaces to enable seamless interaction with data services both on-premises (e.g., ADO.NET Data Services Framework over on-premises SQL Server) and in the cloud (e.g., SQL Server Data Services). Learn how you can leverage existing know-how related to LINQ (Language Integrated Query), data access APIs, data-binding, and more when building applications using online data.

Session 3 – Architecting for the Cloud using Windows Azure - This session provides a tour of the Windows Azure cloud computing platform, an overview of its various components, and explains how these fit together to provide best-of-cloud experiences. We will explore the architecture that links many of the Microsoft .NET services and lets ISVs and businesses deliver compelling solutions. Learn how to compose these services with SQL Data Services to create applications in the cloud and connect them with on-premise systems. We will also examine the next generation of messaging, data, access control, and directory services, and how they fit together to provide a seamless integration into the cloud.

It is an honor to have this Microsoft crew here in Jax, so show your support and Register now!

Speaker’s Bios:
Joe Healy
( http://www.devfish.net ) is the Developer Evangelist for Microsoft Gulf States Accounts, based out of Tampa Florida. Joe’s geographical responsibility is to provide ‘developer care’ for the state of Florida. Joe serves a multitude of clients, from corporate accounts to broad reach events and User Groups. He lectures on various development and architectural topics focused around the .Net Frameworks, Visual Studio.NET, and associated servers. Joe has also served time with eAngler.com, Arthur Andersen, Cap Gemini, EDS, and IBM in various capacities.

Jeff Barnes (Architecture: The Harmony of Mathematical Precision; http://blogs.msdn.com/jbarnes ) is the Microsoft Architect Evangelist for the Gulf States District where he engages with the local Architect community to help solve tough business problems with leading-edge technology. Jeff has been with Microsoft for over 10 years and has spent over 7 of those years as an architect in the Microsoft Consulting Services organization working with large enterprise customers throughout North America. He especially enjoys the freedom and flexibility that .NET brings to the table in order solve the next generation of tough technical challenges.

Be the first to rate this post

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

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

LINQ To XML Questions From Last Night's JaxDUG Meeting

Last night I gave a presentation on data access, OR Modeling and clarification of the various flavors of LINQ. Some of the questions I received were focused on the performance comparisons of LINQ To XML compared to other XML technologies, like XPath and XMLDocument. I came arcross a cousins blog, Eric White...Well maybe he is not my cousin, but probably related in some way. He has some good information by which he researched and made availabe.

Some other info I wanted to share is XML security with LINQ To XML. There is a great MSDN article that sheds some light. Basically, untrusted xml should not be used with LINQ To XML and instead initialized with XMLReader due to 'Denial of Service' attacks, then you can take advantage of LINQ To XML.

Finally, there were questions about LINQ To XML being used with large XML files. Personally, I would not use L2X to perform anything with large xml data files, however I would do something similiar like this CodeProject article recommends.

Be the first to rate this post

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

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