SQL 2008: Calling WCF service from procedure (SQLCLR)

SQLCLR(Common Language Runtime) makes us use .NET framework in SQL   Server. Today, I'm going to talk about this. 


I was developing a module(DLL) which installed on a database server to connect WCF service on different hosts. It's a kind of invoker which means this module is starting point of a specific service. 


After completed to write this, Users would call this module from their web pages or scripts. To do this, the module was registered as a assembly in the database and I also created a CLR stored procedure that points to my module.


A advantage of using CLR in SQL is that T-SQL itself doesn't provide the richer experienced programming environment in comparison with .net framework. We can't use arrays, collection, foreach statement and classes. By integrating CLR, makes it possible to have extensible features in stored procedure, trigger and user defined function.


My module uses .net framework 3.5 and SQL Server 2008 (sp1).
Originally, it built in .net 4.0. but 4.0 hasn't been supported SQL 2008.
So, I changed to 3.5. 


Steps.
1. Go to the management studio of SQL Server, first makes sure we are CLR enabled. 
user master
go
sp_configure 'clr enabled', 1
go
reconfigure
go


2. Set the database trushworthy
alter database [database name]
set trustworthy on;
go


3. Create assemblies. When I created relate assemblies, I got numerous errors. I' going to explain how I made them worked. 


problem 1)
Msg 33009, Level 16, State 2, Procedure usp_xxxxx, Line 0
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YourDatabase’. You should correct this situation by resetting the owner of database ‘YourDatabase’ using the ALTER AUTHORIZATION statement.


=> I executed the below command. 
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false


problem 2)
Msg 10300, Level 16, State 2, Line 1
Assembly 'System.Web' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', 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: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request 

=> I changed 'system.web.dll' path to where based on 64bit. Some example on the web showed path as 'C:\Windows\Microsoft.NET\Framework\...'. When you're deploying your assemblies on 64bit, Its path has to be changed.

create assembly [System.Web]
from  'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'
with permission_set = unsafe


problem 3)
Msg 6522, Level 16, State 1, Procedure 'Procedure Name', Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'Procedure Name': 

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.

System.IO.FileLoadException:

=> I got this error just I executed my procedure (I'd done creation all assemblies). I dropped the assembly named 'System.ServiceModel'. This time, I created sub assemblies which have dependency with 'System.ServiceModel' again.


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


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


CREATE ASSEMBLY  [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 [System.ServiceModel]
from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'
with permission_set = unsafe
go


problem 4)

Msg 6522, Level 16, State 1, Procedure 'Procedure Name', Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'Procedure Name': 

System.Security.SecurityException: 

   at: System.Diagnostics.EventLog.FindSourceRegistration(String source, String machineName, Boolean readOnly)

   at: System.Diagnostics.EventLog.SourceExists(String source, String machineName)

   at: System.Diagnostics.EventLog.VerifyAndCreateSource(String sourceName, String currentMachineName)

   at: System.Diagnostics.EventLog.WriteEntry(String message, EventLogEntryType type, Int32 eventID, Int16 category, Byte[] rawData)

   at: System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type, Int32 eventID, Int16 category, Byte[] rawData)

   at: System.Diagnostics.EventLog.WriteEntry(String source, String message, EventLogEntryType type, Int32 eventID)

=> Let's look at below .NET code. I added a try.. catch statement in regard to raise an error using EventLog.WriteEntry(...). But this isn't allowed to use in SQLCLR. Instead, I use SqlContext.Pipe.Sendf(...). So, I could get the actual error message in SQL management studio when my procedure did it wrong.


[My module]
using Microsoft.SqlServer.Server;    // add this 
......

[SqlProcedure]  //add this
public static void DoService 

{ 
  ......


 try {       
       some coding...


  } catch (Exception ex) {
    // Change to SqlContext.Pipe.send
    SqlContext.Pipe.Send(serviceName + " [DoService]" + ex.Message);

    //EventLog.WriteEntry(serviceName, "[DoService]" + ex.Message, EventLogEntryType.Warning, 1000);
  }
}


After change and execute the procedure, I realized that  'System.Drawing' registered wrong in SQL server. I fixed it with the following command. 

alter assembly [System.Drawing]
from  'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll'
with permission_set = unsafe


Sum up

all created assemblies and their paths are the following.
1) System.Runtime.Serialization
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\System.Runtime.Serialization.dll'

2) SMDiagnostics
'c:\Windows\Microsoft.net\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'

3) System.Web
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll'


4) System.Messaging
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Messaging.dll'


5) System.IdentityModel
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'


6) System.IdentityModel.Selectors
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'      


7) Microsoft.Transactions.Bridge
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'      


8) System.ServiceModel
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'


9) System.Drawing
'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Drawing.dll'


Finally, Don't forget that SQLCLR's data types are different to T-SQL.
For example, we should use NVARCHAR in SQLCRL instead of varchar as parameter for SQLCLR procedure.


Reference sites:
1) http://nielsb.wordpress.com/sqlclrwcf/
2) http://sql-server-stuff.blogspot.com/2008/11/changing-db-owner.html
3) http://msdn.microsoft.com/en-us/library/ms131092.aspx