Accessing MSMQ from Microsoft SQL Server

• Download demo project -3.1 KB
• Download source project - 3.2 KB

The Problem

Sometimes you have to send/receive data using MSMQ from Microsoft SQL Server. Although Microsoft SQL Server 2005 uses MSMQ for replication jobs, there is no functionality in T-SQL to access MSMQ. Let's fill the gap!

Analysis

We have the following solutions:

1. Extended stored procedures - this is an old way to deal with these kind of tasks. You have to write a C++ DLL containing methods that will be called from T-SQL. This is generally not easy and not recommended in Microsoft SQL Server 2005.
2. xp_cmdshell and commandline tools - you have to write commandline tools to send/receive/peek on queue and call them using xp_cmdshell. By default xp_cmdshell is disabled for security reasons (if a malicious user gets control over the SQL Server login privileged to use xp_cmdshell, she or he can easily hack the operating system). The second thing is that you will have to write those tools for MSMQ operations.
3. Windows Service and polling - this is an odd idea but possible. Windows service (proxy) polls the database table for messages to send and listens to the queue for arriving messages that will be put into another table. This looks complex but it is powerful too.
4. CLR assembly - this solution benefits from the new Microsoft SQL Server 2005 feature - CLR integration. In other words, now stored procedures can be written in C#, VB.NET etc.

I'm sure that the first two solutions can be found on the Internet, so we won't deal with them. The third is just another service with ADO.NET stuff. Fortunately there is the last but not the least, and IMO, the most interesting solution. So the choice is easy - CLR assembly.

The Solution

Every piece of code you will see below is in the source package for this article, all T-SQL scripts are in the SqlMSMQRegister.sql file.

Assembly

Let's see through the .NET assembly source code. It's an ordinary class library project with one class SqlMSMQ including three methods Send, Peek and Receive which are the equivalent of System.Messaging.MessageQueue Send, Peek and Receive methods. All methods accept two parameters:

• queue - SqlString with queue path, e.g. myhost\private$\queue1
• msg - SqlString with the content of the message. Of course in Peek and Receive, it's an out parameter

SqlString type is a .NET equivalent of the SQL Server nvarchar type, so for the sake of simplicity, this implementation assumes text messages in MSMQ queues. The most important things here are the Microsoft.SqlServer.Server.SqlProcedure method attributes - they mark which methods can be called from T-SQL as stored procedures. Our methods are very simple, just a try-catch with Send, Peek or Receive inside. After building the assembly, we have everything we need to move to Microsoft SQL Server.

SQL Server 2005

First of all we must enable CLR Integration (disabled by default) in Microsoft SQL Server instance. You can use SQL Server Surface Area Configuration tool (Surface Area Configuration for Features->Database Engine->CLR Integration) or call sp_configure (you must be in 'sysadmin' or 'serveradmin'):

sp_configure 'clr enable', 1
GO

RECONFIGURE
GO

The SQL Server keeps CLR assemblies inside itself. CREATE ASSEMBLY statement uploads an assembly into the SQL Server and creates a database object of a given name.

CREATE ASSEMBLY assembly_name
AUTHORIZATION role_or_user
FROM path_to_dll
WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } 
GO

PERMISSION_SET parameter tells the SQL Server how strict it should be when an assembly accesses resources. SAFE is the default option and the most restrictive - code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry. EXTERNAL_ACCESS option enables the CLR code to access resources like files, registry, network etc. UNSAFE option permits an assembly to access all resources outside and inside SQL Server and to call unmanaged code. In our case we have to use UNSAFE but before creating SqlMSMQ assembly, we need to create other assemblies referenced from SqlMSMQ.

The out-of-box set of system assemblies registered in SQL Server consist of:

• Microsoft.Visualbasic.dll
• Mscorlib.dll
• System.Data.dll
• System.dll
• System.Xml.dll
• Microsoft.Visualc.dll
• Custommarshallers.dll
• System.Security.dll
• System.Web.Services.dll
• System.Data.SqlXml.dll.

Before registering an assembly with PERMISSION_SET = UNSAFE the database must have the TRUSTWORTHY option set to ON:

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON
GO

The missing System.Messaging assembly can be created using the following code:

CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO

This may take awhile because it also creates other assemblies referenced in System.Messaging. Since those DLLs are in the same directory as System.Messaging.dll, they can be automatically registered by the SQL Server. Eventually, we can create the SqlMSMQ assembly:

CREATE ASSEMBLY SqlMSMQ
AUTHORIZATION dbo
FROM '{set path}\SqlMSMQ\Bin\SqlMSMQ.dll'
WITH PERMISSION_SET = UNSAFE
GO

The last thing to do is to create stored procedures that reference methods in SqlMSMQ.dll

CREATE PROCEDURE uspMSMQSend
@queue  nvarchar(200),
@msg    nvarchar(MAX)
AS EXTERNAL NAME SqlMSMQ.[WJeziorczak.Sql.SqlMSMQ].Send
GO

You can find a simple test script in SqlMSMQRegister.sql.

Further Development

Check out other features of CLR Integration like managed triggers, functions and types. Instead of text messages, you can create a custom type object which will be sent through MSMQ. Read more on SQL Server 2005 Books Online.