Click here to Skip to main content
Click here to Skip to main content

Accessing MSMQ from Microsoft SQL Server

, 26 Mar 2007
Rate this:
Please Sign up or sign in to vote.
How to send/receive MSMQ messages in SQL Server using CLR Integration

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Wojtek Jeziorczak
Web Developer
Poland Poland
No Biography provided

Comments and Discussions

 
QuestionRats! What I wanted, but UNSAFE is a showstopper Pinmembermichael d b4-Dec-12 5:17 
AnswerRe: Rats! What I wanted, but UNSAFE is a showstopper Pinmembermichael d b4-Dec-12 12:30 
GeneralRe: Rats! What I wanted, but UNSAFE is a showstopper PinmemberAcidRaZor3-Jan-13 20:51 
QuestionAccess to Message Queuing system is denied PinmemberDivya Ramesh Nagpal18-Jun-12 0:17 
GeneralExcellent article, but not practical for enterprise applications PinmemberEric Coonradt13-Jan-11 10:35 
GeneralSystem.InvalidOperationException/System.IO.FileLoadException PinmemberMember 9869733-Aug-10 5:29 
GeneralError executing sp Pinmemberfrancisco Artacho21-Aug-09 3:15 
GeneraluspMSMQSend Not Working Pinmemberlmax252-Feb-09 9:05 
GeneralRe: uspMSMQSend Not Working PinmemberPradeep Manohar15-Jun-09 12:47 
GeneralRe: uspMSMQSend Not Working PinmemberMember 80879446-Jul-12 11:56 
Generalvery helpfull example Pinmemberstancosti5-Aug-08 9:27 
GeneralFinally found a good comparison software Pinmemberdingoishere2-Jul-08 1:19 
GeneralTransactional Queue Pinmemberal_todd25-Jun-08 1:46 
GeneralUNSAFE !!! Pinmemberdamnedyankee20-Mar-08 7:14 
QuestionAccessing MSMQ from Microsoft SQL Server 2000 PinmemberEnas KH19-Feb-08 15:40 
QuestionDotnet framework error Pinmemberdmw12313-Dec-07 12:02 
AnswerRe: Dotnet framework error PinmemberMember 80879446-Jul-12 11:53 
QuestionAssembly not fully tested in SQL Server. PinmemberHomeJAS27-Nov-07 6:43 
Hi,
after Creating the assembly for messaging I get following message for several dll's - what's your thought on this?
 
Warning: The Microsoft .Net frameworks assembly 'system.messaging, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 26 Mar 2007
Article Copyright 2007 by Wojtek Jeziorczak
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid