WCF Client Inside SQL CLR

Question

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."

Answer 1

At least in SQL Server 2005, there were only a handful of supported assemblies for SQL CLR. I don't believe WCF was one of them. I did a quick search, and nothing turned up to lead me to the conclusion that much has changed for 2008 SQL CLR.

Reference: http://support.microsoft.com/kb/922672

Answer 2

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

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.

Answer 3

I had similar problems while trying to call WCF web services within SQL CLR. It loads so much assemblies from .NET and I couldn't make it work.

Eventually, I use generic WebClient to make HTTP request to the WCF server which works really well and it requires min assemblies loaded into SQL Server.

Answer 4

I've the same problem with System.Runtime.Serialization assembly. over a x64 windows

The solution was, find the System.Runtime.Serialization in c:\windows\assembly directory, copy it to other directory and run the CREATE ASSEMBLY sql command.

Best,