Search code examples
.netsqlwcfsqlclr

WCF Client Inside SQL CLR


I know it's not supported, and I know it's not even a terribly good idea. But, I want to have a WCF client inside a SQL Table Valued Function.

I've (seemingly) registered the correct assemblies, but when running my client - I get a WCF error:

Msg 6522, Level 16, State 1, Line 1
System.ServiceModel.CommunicationObjectFaultedException: 
 The communication object, System.ServiceModel.ChannelFactory`1[MyProxy.IMyService], 
 cannot be used for communication because it is in the Faulted state.

A test outside of Sql Server seems to work - and I don't even see the WCF client trying to make a TCP connection.

Not sure if this is a WCF or SQL CLR issue, as I'm new to both.....

Edit: I understand that the required System.ServiceModel and it's umpteen assemblies are outside of the vetted Sql CLR list. However,

"Unsupported libraries can still be called from your managed stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. The unsupported library must first be registered in the SQL Server database, using the CREATE ASSEMBLY statement, before it can be used in your code. Any unsupported library that is registered and run on the server should be reviewed and tested for security and reliability."


Solution

  • This article might help you find out what the true underlying exception is and to see how fatal it is:

    http://jdconley.com/blog/archive/2007/08/22/wcfclientwrapper.aspx (archive.org copy)

    As David correctly says, the SQL CLR has support for a limited subset of .NET assemblies:

    SQL 2005 Supported .NET Framework Libraries
    SQL 2005 CLR Integration Programming Model Restrictions

    SQL 2008 Supported .NET Framework Libraries
    SQL 2008 CLR Integration Programming Model Restrictions

    System.Web.Services is supported if the end point is a WSDL service so that might help you get out of a hole? Or you could always use a web service as a proxy to whatever non-webservice WCF endpoint you're trying to talk to.