CLR Procedure, next iteration

We have code that uses an old Extended Stored Procedure written in Delphi that is giving some problems, so I decided to rewrite the procedure in C# as a CLR procedure.

The code has to download emails from a POP mailbox and process the attachments. For the POP code I found a nice POP library: http://dotnetctrlext.sourceforge.net/smtpop.htm

I had to exclude the MessageBuilder class from being compiled, as it was referencing System.Drawing, with which SQL Server has some issues, and I did not want to delve into them forever.

The first challenge was to register the CLR procedures. The database needs TRUSTWORTHY property set to ON, because the CLR procedure needs to be created with UNSAFE permissions as it accesses the network for the POP mail download.

This is the error it gives:

CREATE ASSEMBLY for assembly 'IntRegPopUtil' failed because assembly 'IntRegPopUtil' is not authorized for PERMISSION_SET = UNSAFE.  
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the 
TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. 
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. 
If not, use sp_changedbowner to fix the problem.

This is the change necessary:

ALTER DATABASE dbname SET TRUSTWORTHY ON

Next is creating the assembly:

create assembly IntRegPopUtil from 'C:\work\sandbox\IntRegPopUtil\bin\Debug\IntRegPopUtil.dll' with permission_set = UNSAFE

And the CLR Procedure:

create procedure IntRegPopProcessMail as external name IntRegPopUtil.[IntRegPopUtil.IntRegBulkMail].ProcessMail

Now we are ready to execute the procedure.

If you get an error like the following when executing your procedure, you need to create the assembly with UNSAFE permissions, as above.

A .NET Framework error occurred during execution of user-defined routine or aggregate "IntRegPopProcessMail": 
System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at SmtPop.POP3Client.Open(String pop3host, Int32 port, String user, String pwd)
   at IntRegPopUtil.IntRegBulkMail.ProcessMail()

The next challenge was to get my code to be able to read its appSettings from sqlservr.exe.config in the SQL Server Binn folder.

You have to restart SQL after creating the sqlservr.exe.config in the same folder as sqlservr.exe. Then the fun starts.

It kept on giving error like the following:

"The value of the property 'key' cannot be parsed.
* The error is: Request failed. (sqlservr.exe.Config line X)".

This seems to be a bug from SQL2005 beta days, the solution is to add a dummy call to the ConnectionStrings property, this causes it to initialize properly. I found the solution here.

int i = ConfigurationManager.ConnectionStrings.Count;

That's it, now the procedure can connect to the POP server to download the mail, and it can use settings from the <appSettings> section in the sqlservr.exe.config.

To update the assembly when you've made changes to the dll, use the following syntax:

alter assembly IntRegPopUtil from 'C:\work\sandbox\IntRegPopUtil\bin\Debug\IntRegPopUtil.dll'

Creating a SQL Server CLR Procedure

I ventured into unchartered territory (for myself) and created a SQL Server CLR Procedure today. It is actually very easy.

First, create a class library project in VS 2005/2008. Create a class with a static method that will be the CLR Procedure

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace SQLCLRTest
{
public class SQLCLRDemo
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
   using (SqlConnection conn =
        new SqlConnection("context connection = true"))
   {
       conn.Open();
       SqlCommand cmd = new SqlCommand();
       cmd.Connection = conn;
       cmd.CommandText = "SELECT 'Hello World!' AS Greeting";
       SqlContext.Pipe.ExecuteAndSend(cmd);
   }
}
}
}

Then you have to register the assembly in SQL Server

CREATE ASSEMBLY SQLCLRTest FROM 'C:\work\sandbox\SQLCLRTest\SQLCLRTest\bin\Debug\SQLCLRTest.dll'

Then you create the procedure

CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME SQLCLRTest.[SQLCLRTest.SQLCLRDemo].HelloWorld

You might have to enable CLR execution in SQL2005

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

Then you can appreciate the fruits of your labour

exec HelloWorld

Greeting
------------
Hello World!

(1 row(s) affected)

Now that was easy!

MSDN reference here

Error when creating CLR procedure here

Enabling CLR in SQL Server here