Setting up a Message Queue in SQL Server

Over the years I’ve had a few instances where I’ve had to set up a message queue with the broker in SQL Server and every time I come back to set up another queue I find the documentation to be very confusing. So here is my attempt to make the setup easier to follow. Please take note this has been simplified from the documentation that you can find here.

Setting up the Message Queues.

To begin with you need to make sure that the database that your working with has the broker enabled.

Now both message types need to be created the requesting message and the response message. The requesting message is the one that initiates the communication and the response message is the response to the request.

Now we need to tell SQL Server about the contract between the messages, which basically tells the server which is the requesting message and which is the response message.

After the contract we need to create a queue and service for each message that will be transmitted. Here is the code for the request message.

Then we create the queue for the response message and make sure it’s connected to the contract.

Setting up the stored procedures to handle the communication.

After everything is setup we need to create a stored procedure that will send the request message to the queue. When using this, remember that the request message needs to be valid xml.

Now we need to setup a stored procedure that will receive the request message and send a response back to whoever sent the request.

And for the very last part, we need to receive the response message.

Mike GriffithSetting up a Message Queue in SQL Server