Call a WCF Service from a SQL CLR procedure in C#

Question

I'm trying to call a WCF Service from a SQL Stored Procedure written in C#.

I saw various posts or questions on about the same topic : http://stackoverflow.com/questions/3502343/calling-a-wcf-service-from-sql-clr-stored-procedure http://stackoverflow.com/questions/751500/sql-clr-stored-procedure-and-web-service

But there's something I don't get. To be able to call my WCF Service from the Stored Procedure I'm creating the WCF client in the C# code of the procedure:

    //Create an endpoint addresss for our service
    public static EndpointAddress endpoint =
      new EndpointAddress(new Uri("http://localhost:32226/ServiceName.svc"));
    //Create a binding method for our service
    public static WSHttpBinding HttpBinding = new WSHttpBinding();
    //Create an instance of the service proxy
    public static ChannelFactory<IServiceName> MyChannelFactory = new ChannelFactory<IRecursosHumanos>(HttpBinding, endpoint);

    // Create a channel.
    public static IRecursosHumanos ServicioRrhh = MyChannelFactory.CreateChannel();

I'm compiling the project as a .NET 3.0+ to be able to compile it (Reference to Sytem.ServiceModel). When I try to deploy it on the SQL Server, I'm getting the following message:

Msg 10301, Level 16, State 1, Line 2
Assembly 'MyAssembly' references assembly 'system.runtime.serialization, version=3.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(failed to retrieve text for this error. Reason: 15105)). Please load the referenced assembly into the current database and retry your request.

Should I register this assembly also on the server? as the assemblies needed for WCF? Wouldn't I damage the server adding so many assemblies?

Thanks for your help.

Answer 1

It should work if you use a Web Reference in the CLR assembly instead of a Service Reference.

Answer 2

SQL Server will need your assembly in order to load the CLR Sproc as it's compiled with it. You need to add it to the GAC on the Server. I don't know what you mean 'damage the server'