Register Clr Function (Wcf Based) in SQL Server 2012

I cannot use WCF in SQL CLR 2012

No, SQL-Server only allows a rather limited subset of C#, for security reasons.

For example, adding a reference to System.Web is sufficient that it doesn't work.

Perhaps you can rip the part of System.ServiceModel that you need out of the mono sourcecode and get it to work that way. With luck, if you need only one or two function calls, so you can get it to work with a few changes.

On a sidenote: If you could add WCF in a CLR-Stored Procedure, you could make SQL-Express use HTTP-Endpoints. But one of the defining limitations of SQL-Server Express is, that the Express version doesn't allow that. If that was possible, you could circumvent that limitation, and Microsoft certainly doesn't want that.

See also this post:

WCF Client Inside SQL CLR

SQL CLR - Migration from 2008 R2 to 2012.

I was able to get to the root cause of the issue I was facing. SQL 2012 supports .NET framework version 4.0 for SQLCLR and hence loads all framework assemblies from 4.0 framework. Hence I needed to upgrade all my assemblies to use 4.0 framework assemblies. http://blogs.msdn.com/b/dohollan/archive/2012/04/20/sql-server-2012-sqlclr-net-framework-version.aspx?CommentPosted=true#commentmessage

But things didnt work for me even after that. It turns out in 4.0, .NET changed some framework assemblies like System.ServiceModel from Pure .Net assemblies to mixed mode assemblies. Such assemblies cannot be loaded by SQL CLR. So all user defined assemblies depending on mixed mode assemblies pre 2012 dont work anymore on 2012 :(
Most badly hit are previously working SQL and WCF integrations completely breaking in 2012 sicne WCF depends on ServiceModel.
More here http://blogs.msdn.com/b/psssql/archive/2013/02/23/unable-to-register-net-framework-assembly-not-in-the-supported-list.aspx

We ended up writing a simple Webservice wrapper over our WCF operation and writing a Webservice client in SQLCLR.

WCF Client Inside SQL CLR

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.

SQL Server CLR : how to call WCF Service In CLR SQL stored procedure in database project

I found this after a lot of searching and spend very much time on this in VS 2014

1. Create Database Project Called "CLR_Test"
2. Create Library For WCF Client "CLR_Service_Client"

3. Add Serivce Refrence of wcf service to "CLR_Test" then add refrence of "CLR_Service_Client" into "CLR_Test"
   
   4.You must change DB Option to able run unsafe assemblyes with the below code

   ALTER DATABASE SaleAutomation SET TRUSTWORTHY ON
RECONFIGURE

4. In the "CLR_Test" Project Properties in the SQLCLR tab set Permission level to Unsafe (another way is exist that after publish project you change its level from sql server management and another way is you add permission level to script of publish you can use each of them,
   
   but you must noticed that if you use from project properties only "CLR_Test" project automaticly create Unsafe and you must use other ways to set "CLR_Service_Client" Unsafe )
   
   
   6.Run this Scripts to add Sqlserver be able to run wcf service

CREATE ASSEMBLY 
SMDiagnostics from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY 
[System.Web] from
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY 
[System.Messaging] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
with permission_set = UNSAFE
 GO

CREATE ASSEMBLY  
[System.IdentityModel] from
'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY  
[System.IdentityModel.Selectors] from
'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY -- this will add service modal
[Microsoft.Transactions.Bridge] from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'
with permission_set = UNSAFE
GO

CREATE ASSEMBLY -- this will add service modal
[System.Runtime.Serialization] from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY -- this will add service modal
[System.ServiceModel] from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.ServiceModel.dll'
with permission_set = UNSAFE
GO

7. now you publish your project and run stored procedure and enjoy.

CLR-based UDT and string property in SQL Server 2008R2

The link contains the answer (Figure 5), which is implementing your own serializer: http://msdn.microsoft.com/en-us/magazine/cc164013.aspx

SQL Server 2016: CREATE ASSEMBLY for assembly 'System.ServiceModel' failed

No, there is no fix that will allow for loading ServiceModel. The problem is that it is an unsupported .NET Framework library. This library did work in SQL Server 2005, 2008, and 2008 R2 as they were linked to CLR version 2.0. However, since it is not in the "supported" list, it was never guaranteed to work across all .NET Framework upgrades. Hence, while it used to be a pure-MSIL Assembly, they changed it to be a mixed-mode Assembly, and those cannot be loaded into SQL Server, starting with SQL Server 2012.

This issue has been documented on Stack Overflow here:

• Register CLR function (WCF based) in SQL Server 2012

• SQL CLR - Migration from 2008 R2 to 2012.

• (Unfixable) Assembly exists on SQL Server 2014 but it claims it doesn't have it

More info here:

• SQL Server: "CREATE ASSEMBLY for assembly 'Test' failed because assembly 'Test' is malformed or not a pure .NET assembly."

• SQL Server custom CLR fails with error "Could not load file or assembly or one of its dependencies. The system cannot find the file specified."

Error deploying WCF Client - 'system.servicemodel was not found in SQL Catalog'

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

---

Related Topics