SQL Server Service Broker example on how to configure, send and receive messages

By:Jayakumar KrishnanLast Updated: 2009-09-14Comments (9)Related Tips:MoreService Broker

SQL Server 2008 and 2005 offer the Service Broker feature. In this tip we will go through the different components of service broker and step by step on how to setup Service Broker for a single database.

What is service broker? Service Broker is a process of sending and receiving guaranteed, asynchronous messages by using extensions to the Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.

To configure the service broker to send and receive messages between databases (either in local/remote instances), it needs a few components in place to start the service. These are listed below.

Note: For my test Ive created a database namedServerBrokerTest, which Im going to use in this article. Ive used a single database to make this easy to understand. We can call it a single database conversation.

Service broker is a database level feature not an instance level. So it has to be enabled before starting the other configurations.

Use the following code to enable the Service Broker.

Enabling service broker option can be verified with the Is_Broker_Enabled column (set to true [1]) in sys.databases catalog view.

Service Broker needs specific Message Types to send and receive messages, which have to be created before the communication starts. The initiator (Sender/Requestor) and the target (Receiver/Reply) have to use the same Message Type or else the communication will fail. The most common used Message Type is WELL_FORMED_XML.

The following example code is used to create a Message Type in the same database for the Initiator (Requestor/Sender) and the Target (Reply/Receiver). Use the following code to create the Message Types.

–Create Message Types for Request and Reply messages USE ServiceBrokerTest — For Request CREATE MESSAGE TYPE [//SBTest/SBSample/RequestMessage] VALIDATION=WELL_FORMED_XML; — For Reply CREATE MESSAGE TYPE [//SBTest/SBSample/ReplyMessage] VALIDATION=WELL_FORMED_XML;

Service Broker requires a contract to send and receive messages in a single or between multiple databases. The contract will ensurewhich Message Type is going to be used to send and receive messages between the Initiator (Requestor/Sender) and the Target (Reply/Receiver). Use the following code to create the contract.

–Create Contract for the Conversation USE ServiceBrokerTest CREATE CONTRACT [//SBTest/SBSample/SBContract] ( [//SBTest/SBSample/RequestMessage] SENT BY INITIATOR ,[//SBTest/SBSample/ReplyMessage] SENT BY TARGET );

The Service Broker Queue is a Message Storage container which is used to keep the messages while sending and receiving. The below code creates two queues, one is the Initiator (Requester/Sender) and the other is Target (Receiver/Replier). Queues will be used by the Service Broker Services.

Use the following code is to create the Queues.

The Service Broker Services route the Messages to the specific Queues. The Service and Queue are bonded to each other. When the Initiator or the Target send a Message, the service will route the messages to the appropriate Queues.

Use the following code to create the Service Broker Service.

–Create Service for the Target and the Initiator. USE ServiceBrokerTest –Create Service for the Initiator. CREATE SERVICE [//SBTest/SBSample/SBInitiatorService] ON QUEUE SBInitiatorQueue; –Create Service for the Target. CREATE SERVICE [//SBTest/SBSample/SBTargetService] ON QUEUE SBTargetQueue ([//SBTest/SBSample/SBContract]);

Note: In the above code Ive not specified the Contract name for the Initiator Service, but I specified for the Target Service, which means if no Contact name is specified on a Service then the Service can only initiate conversations but no other Services can use that service as a Target (Reply/Receiver).

In this section, Ill describe how to Send (Request – from the Initiator) and Reply (from the Target) and Receive a message between the Initiator and the Target. We can understand the conversation cycle between the Initiator and Target.

The following code sends a request message from the Initiator to the Target. The code can be split into three parts.

Sending a message is a single transaction, which includes all 3 items above.

–Sending a Request Message to the Target USE ServiceBrokerTest DECLARE @InitDlgHandle UNIQUEIDENTIFIER DECLARE @RequestMessage VARCHAR(1000) BEGIN TRAN –Determine the Initiator Service, Target Service and the Contract BEGIN DIALOG @InitDlgHandle FROM SERVICE [//SBTest/SBSample/SBInitiatorService] TO SERVICE //SBTest/SBSample/SBTargetService ON CONTRACT [//SBTest/SBSample/SBContract] WITH ENCRYPTION=OFF; –Prepare the Message SELECT @RequestMessage = NRequestMessage Send a Message to Target /RequestMessage; –Send the Message SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//SBTest/SBSample/RequestMessage] (@RequestMessage); SELECT @RequestMessage AS SentRequestMessage; COMMIT TRAN

Note: TO SERVICE needs to be specified in the single quotes because it is case sensitive, service broker uses a byte-by-byte comparison with the Target service name. The above code will give the below result set.

The following code receives a message from the Initiator and sends a Reply message to it.

–Receiving a Message and sending a Reply from the Target USE ServiceBrokerTest DECLARE @TargetDlgHandle UNIQUEIDENTIFIER DECLARE @ReplyMessage VARCHAR(1000) DECLARE @ReplyMessageName Sysname BEGIN TRAN; –Receive message from Initiator RECEIVE TOP(1) @TargetDlgHandle=Conversation_Handle ,@ReplyMessage=Message_Body ,@ReplyMessageName=Message_Type_Name FROM SBTargetQueue; SELECT @ReplyMessage AS ReceivedRequestMessage; — Confirm and Send a reply IF @ReplyMessageName=N//SBTest/SBSample/RequestMessage BEGIN DECLARE @RplyMsg VARCHAR(1000) SELECT @RplyMsg =NRplyMsg Send a Message to Initiator/RplyMsg; SEND ON CONVERSATION @TargetDlgHandle MESSAGE TYPE [//SBTest/SBSample/ReplyMessage] (@RplyMsg); END CONVERSATION @TargetDlgHandle; END SELECT @RplyMsg AS SentReplyMessage; COMMIT TRAN;

The above will give two result sets as below, 1 is received message from the Initiator and 2 is the sent message to the Initiator from the Target.

ReceivedRequestMessage RequestMessage Send a Message to Target /RequestMessage SentReplyMessage RplyMsg Send a Message to Initiator/RplyMsg

The below code receives a reply message from the Target.

–Receiving Reply Message from the Target. USE ServiceBrokerTest DECLARE @InitiatorReplyDlgHandle UNIQUEIDENTIFIER DECLARE @ReplyReceivedMessage VARCHAR(1000) BEGIN TRAN; RECEIVE TOP(1) @InitiatorReplyDlgHandle=Conversation_Handle ,@ReplyReceivedMessage=Message_Body FROM SBInitiatorQueue; END CONVERSATION @InitiatorReplyDlgHandle; SELECT @ReplyReceivedMessage AS ReceivedRepliedMessage; COMMIT TRAN;

Below is the output of the above transaction, which confirms that the reply message received from the Target.

That is pretty much all there is to setting up Service Broker and sending and receiving some simple messages.

The following catalog views are helpful to check the usage of the Service Broker Objects.

–Checking the usage of the Messages, Contracts and Queues using System views. USE ServiceBrokerTest SELECT * FROM sys.service_contract_message_usages SELECT * FROM sys.service_contract_usages SELECT * FROM sys.service_queue_usages

I hope this article has taught you how to configure the Service Broker in single database and start the conversation cycle between the Initiator and Target. Conversation between databases also has the same steps, but the initiator and the target will be two different databases and the services and queues have to be created in both of the databases.

Review these other tips related to Service Broker

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*** NOTE *** – If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

Hi this is very useful and very nice thanks friend

Thanks for the examples. Make me get clearer picture.

Actually a real example of using a conversation 2-ways, very nice 🙂 Many other examples seem to forget to reply, well… should I say all others 🙂

to produce a reult as 1 without entering numeric values in select statement

Block of T-SQL inReceiving a Reply Message from the Targetdoesnt properly work. I catch only error Msg 8418, Level 16, State 1, Line 11 conversation handle is missing. Specify a conversation handle.Also i cannt delete services, queues and contract

that so usefull and simple so thanks dear friend

Excellentjob, very helpful Ive passed this on to the team here. Thank you for taking the time to do this.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read theprivacy statementand understand I may unsubscribe at any time.

Copyright (c) 2006-2019Edgewood Solutions, LLCAll rights reserved

Some names and products listed are the registered trademarks of their respective owners.