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 : Calling a WCF Service from SQL CLR Stored Procedure 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

After 2 days of trial and error, I've finally got it working, like so:

(On a Windows Server 2008 x64, SQL Server 2008 64 bit, Assembly DLL built with Framework 3.0, Any CPU). I suggest you stick with .Net 3 for SQL 2008.

alter database [TTBackup]
set trustworthy on;
go
USE TTBackup
--Register the assemblies referenced by our assembly.
CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.IdentityModel.Selectors] FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll' with permission_set = unsafe 
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [Microsoft.Transactions.Bridge] FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll' with permission_set = unsafe 

--register our assembly
CREATE ASSEMBLY [TTDatabaseIntegration] AUTHORIZATION dbo FROM 'D:\TTDatabaseIntegration.dll' WITH permission_set = unsafe
GO
--Register the UDF from CLR
CREATE FUNCTION [ListImportTemplates]( ) RETURNS TABLE(TemplateID int, TemplateName NVARCHAR(4000))
AS EXTERNAL NAME [TTDatabaseIntegration].[UserDefinedFunctions].[ListImportTemplates]
GO 

--Test my function 
select  * FROM dbo.[ListImportTemplates]()

Notice that most of the referenced DLLs are 32 bit, but System.Web is 64 bit. (It's the only way it works for me).

Notes:

1. At first I've registered 64 bit assemblies, but it was throwing a runtime exception:System.IO.FileLoadException: Could not load file or assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

   I ended up comparing the files (by size) in GAC to the ones i've been registering in SQL. (GAC is located at C:\Windows\assembly, but you cannot view the folder in Explorer, I've been using Total Commander for this.) This is how I found that the version in GAC is the 32 bit version.

2. You can view the registered assemblies with this query: SELECT * FROM sys.assemblies

References (explaining step-by-step):

1. http://nielsb.wordpress.com/sqlclrwcf/
2. http://hyper-choi.blogspot.ro/2011/07/sql-2008-calling-wcf-service-from.html

Answer 3

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'