Call WCF service from SQL CLR procedure in C #

Question

I am trying to call a WCF service from a SQL stored procedure written in C #.

I saw different messages or questions on one topic: Calling WCF service from SQL CLR stored procedure Stored procedure and SQL CLR web service

But something I won’t get. To be able to call my WCF service from a stored procedure, I create a 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 am compiling the project as .NET 3.0+ to compile it (link to Sytem.ServiceModel). When I try to deploy it to SQL Server, I get 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.

Do I have to register this assembly on the server as well? How are the builds needed for WCF? Wouldn't I damage a server adding so many assemblies?

Thank you for your help.

Answer 1

, - CLR Service Reference.

Answer 2

2- , - , :

( Windows Server 2008 x64, SQL Server 2008 64 , DLL, Framework 3.0, ). .Net 3 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]()

, DLL, , 32 , System.Web - 64 . ( , ).

:

• 64- , : 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.

  ( ) GAC , SQL. (GAC C:\Windows\assembly, , Total Commander . ) , GAC - 32- .

• : SELECT * FROM sys.assemblies

( ):

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

Answer 3

SQL Server will need your assembly to load the Sproc CLR when it is compiled with it. You need to add it to the GAC on the server. I do not know what you mean by "damage the server"