Click here to Skip to main content
15,311,014 members
Articles / Database Development / SQL Server
Posted 26 Mar 2007


64 bookmarked

Accessing MSMQ from Microsoft SQL Server

Rate me:
Please Sign up or sign in to vote.
4.95/5 (13 votes)
26 Mar 20074 min read
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!


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.


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


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

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:


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

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'

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:

FROM '{set path}\SqlMSMQ\Bin\SqlMSMQ.dll'

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

@queue  nvarchar(200),
@msg    nvarchar(MAX)

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.


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


About the Author

Wojtek Jeziorczak
Web Developer
Poland Poland
No Biography provided

Comments and Discussions

QuestionRats! What I wanted, but UNSAFE is a showstopper Pin
michael d b4-Dec-12 4:17
Membermichael d b4-Dec-12 4:17 
AnswerRe: Rats! What I wanted, but UNSAFE is a showstopper Pin
michael d b4-Dec-12 11:30
Membermichael d b4-Dec-12 11:30 
GeneralRe: Rats! What I wanted, but UNSAFE is a showstopper Pin
AcidRaZor3-Jan-13 19:51
MemberAcidRaZor3-Jan-13 19:51 
QuestionAccess to Message Queuing system is denied Pin
Divya Ramesh Nagpal17-Jun-12 23:17
MemberDivya Ramesh Nagpal17-Jun-12 23:17 
GeneralExcellent article, but not practical for enterprise applications Pin
Eric Coonradt13-Jan-11 9:35
MemberEric Coonradt13-Jan-11 9:35 
GeneralSystem.InvalidOperationException/System.IO.FileLoadException Pin
Member 9869733-Aug-10 4:29
MemberMember 9869733-Aug-10 4:29 
GeneralError executing sp Pin
francisco Artacho21-Aug-09 2:15
Memberfrancisco Artacho21-Aug-09 2:15 
GeneraluspMSMQSend Not Working Pin
lmax252-Feb-09 8:05
Memberlmax252-Feb-09 8:05 
GeneralRe: uspMSMQSend Not Working Pin
Pradeep Manohar15-Jun-09 11:47
MemberPradeep Manohar15-Jun-09 11:47 
GeneralRe: uspMSMQSend Not Working Pin
Member 80879446-Jul-12 10:56
MemberMember 80879446-Jul-12 10:56 
Generalvery helpfull example Pin
stancosti5-Aug-08 8:27
Memberstancosti5-Aug-08 8:27 
GeneralFinally found a good comparison software Pin
dingoishere2-Jul-08 0:19
Memberdingoishere2-Jul-08 0:19 
GeneralTransactional Queue Pin
al_todd25-Jun-08 0:46
Memberal_todd25-Jun-08 0:46 
GeneralUNSAFE !!! Pin
damnedyankee20-Mar-08 6:14
Memberdamnedyankee20-Mar-08 6:14 
QuestionAccessing MSMQ from Microsoft SQL Server 2000 Pin
Enas KH19-Feb-08 14:40
MemberEnas KH19-Feb-08 14:40 
QuestionDotnet framework error Pin
dmw12313-Dec-07 11:02
Memberdmw12313-Dec-07 11:02 
AnswerRe: Dotnet framework error Pin
Member 80879446-Jul-12 10:53
MemberMember 80879446-Jul-12 10:53 
QuestionAssembly not fully tested in SQL Server. Pin
HomeJAS27-Nov-07 5:43
MemberHomeJAS27-Nov-07 5:43 

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

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