MSMQ and SQL Server integration

At times we have long running SQL procedures and its not always advisable to have the application waiting on it.Especially if your application needs to perform certain set of tasks based on the result returned from the SQL operation.

There are a few approaches that one might consider to address this scenario and using the Service Broker is definitely one of them. But we are not going to look into Service Broker, rather look at how we can use Messaging Queuing with SQL Server to address this requirement.


SQL Server interaction with MSMQ

The following steps depicts the process involved.

  • The SQL Server runs a Scheduled Job that does the following
    • Execute the long running Stored procedure
    • Execute a stored procedure that sends a message to a remote Message Queue
  • Remote queue on the application server receives the message
  • Client application running on the application server polls for messages from the MSMQ. On receiving the message, the message is read from the MSMQ and application can resume its further processing

In this post we will cover all the configuration, components and environment settings including (ports, firewall rules, queue access, SQL configuration etc.) required to facilitate this setup.

.Net Class Library

This class library will simply send the message to a MSMQ. This defines a simple method that takes the queue name and the message to be delivered as parameters.

using System;
using System.Data.SqlTypes;
using System.Messaging;
using Microsoft.SqlServer.Server;

namespace SqlMsmq
    public class SqlToMsmqManager
        /// <summary>
        /// Sends a message to the queue
        /// </summary>
        /// <param name="queueName">Full name of the queue</param>
        /// <param name="message">Message to send</param>
        public static void Send(SqlString queueName, SqlString message)
            if (queueName == null || string.IsNullOrEmpty(queueName.Value))
                throw new Exception("Message queue name need to be provided");

            var queue = queueName.Value;
            if (!MessageQueue.Exists(queue))
                throw new Exception("Message queue does not exist");
                using (var messageQueue = new MessageQueue(queue, QueueAccessMode.Send))
                    messageQueue.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });
                    messageQueue.Send(message.Value, MessageQueueTransactionType.Single);
            catch (Exception ex)
                throw ex;

SQL Server

To be able to send a message to a Message Queue from SQL Server, first of all we will need to register the System.Messaging assembly and the assembly we created above with SQL Server. Below is the script that defines different steps required in SQL Server configuration

-- STEP1: Enable CLR integration in SQL Server
SP_CONFIGURE 'clr enable', 1

USE [DatabaseName]

-- STEP 2: Add System.Messaging assembly to the database to enable Message Queuing component
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll' -- Path to the System.Messaging.dll assembly

-- STEP 3: Add the external .Net assembly that will send the message to the queue
FROM 'C:\SqlToMsmq\SqlMsmq.dll' -- Path to the .Net class library

-- STEP 4: Create procedure that will calls into the external .Net assembly to send the message
	@queueName NVARCHAR(200),
	@message NVARCHAR(MAX)
	EXTERNAL NAME SqlToMsmq.[SqlMsmq.SqlToMsmqManager].Send

--Stored Procedure that needs to be called from the escowing SP at the end
EXEC SendMsmqMessage '<Full queue name to send the message after SP runs>', '<Message><Status>Stored procedure processed</Status></Message>'


  • You will need to be an admin user to be able to  make the above configurations to SQL server
  • Sometimes when registering assemblies in SQL Server you would receive the error:
    "The database owner SID recorded in the master database differs from the database owner SID recorded in database.You should correct this situation by resetting the owner of database using the ALTER AUTHORIZATION statement."

    In this case run the following statement to alter the authorization on the database



Because in the class library we have instructed that the message queue is a transactional queue, we need to create a transactional queue on the application server

Environment Configuration

MSMQ communicates over ports 135, 1801, 2103/2105

  • Sending Messages

So, since our database server will be pushing the message to MSMQ, we need to have port 1801 on the database server. MSMQ uses this port to establish a network session and then push the message to the destination

  • Receiving Messages

MSMQ uses RPC for pulling messages, which requires ports 135, 2103 or 2105 open on the application server.

Environment Testing

To test that you have the required level of connectivity between your database and application server for sending MSMQ messages you should at least be able to telnet from your database server to your application server on port 1801

telnet 1801

And lastly

To send the message from your SQL Server to the MSMQ queue as defined above all we have to do is:

EXEC SendMsmqMessage 'FormatName:Direct=OS:ServerName\Private$\QueueName', '<Message>Any message</Message>'


  • Since our queue is a private queue we need to specify the message queue name in complete format.
    • We can use either OS or TCP format specifier

And that’s it. The message should appear in the message queue.

About Jinish Bhardwaj
Jinish works as a Software Architect for Tucows and has more than 14 years of experience in building high availability real time Web, Windows and Smart Client systems for clients across the globe. Jinish is also a Microsoft Certified Professional and a Certified SCRUM Master

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: