Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 1, 2010

Six Key SQL Server Service Broker Components and How They Relate

By Arshad Ali

Arshad Ali discusses the SQL Server Service Broker Components (SSBS), Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components relate to each other.


In my last article (SQL Server Service Broker Components - Message Types, Contracts and Queue), I discussed the Initiator, Target, Message Types, Contract and Queue components of SSBS. In this article I will be talking about the Service, Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components are related to each other.

Service Broker – Components – Cont’d


A service represents an endpoint for message sending/receiving and is associated with a queue. It enforces the defined contract for the conversation and plays a role for routing and delivering the message to the target queue. On target, the Target service picks up the messages from the target queue and processes them. The general syntax to create a service is given below:

CREATE SERVICE <service_name>
   [ AUTHORIZATION <owner_name> ]
   ON QUEUE <queue_name>
   [ ( <contract_name> | [DEFAULT] [ ,...n ] ) ]

<service_name> is the name of the service that you want to create. <owner_name> owns the service and if not specified will be owned by the current user. <queue_name> is the name of the queue with which you want this service to be associated and it must exist in the same database as of the service. Then you specify one more <contract_name>, which this service will be using to validate the messages. [DEFAULT] is the name of default contract, which exists in every database and which has DEFAULT message type whose validation is set to NONE. If you omit the specification of contract then service might only initiate a conversation.

Example – The statement below will create a target service called [//SSBSLearning/ProductStockStatusCheck/TargetService]which is associated with dbo.SSBSLearningTargetQueue and uses the ([//SSBSLearning/ProductStockStatusCheckContract] contract.

       ON QUEUE dbo.SSBSLearningTargetQueue

Conversation and Conversation Groups

A conversation is the exchange of messages between two endpoints; the service that initiates the conversation is called the Initiator and the service, which receives the conversation request, is called the Target. A conversation group is an ordered set of related conversations. Every message of the conversation is assigned a sequence number and SSBS ensures the messages are processed in order. Starting with SQL Server 2008, you can define the conversation group priority; it means a high priority conversation group will be processed before a low priority conversation group but conversations inside a conversation group are still processed in order. SSBS uses a new type of lock i.e. ‘conversation group lock’ to ensure that only one activation procedure processes the conversations of a conversation group in order.

   FROM SERVICE <initiator_service_name>
   TO SERVICE '<target_service_name>'
       [ , { 'service_broker_guid' | 'CURRENT DATABASE' } ]
   [ ON CONTRACT <contract_name> ]
   [ WITH
   [  { RELATED_CONVERSATION = <related_conversation_handle>
      | RELATED_CONVERSATION_GROUP = <related_conversation_group_id> } ]
   [ [ , ] LIFETIME = <dialog_lifetime> ]
   [ [ , ] ENCRYPTION = { ON | OFF }  ] ]

When you start a conversation with the BEGIN DIALOG command, it returns a dialog handle of the type uniqueidentifier, which you place into a variable to later use while sending messages. FROM SERVICE and TO SERVICE clauses are used to specify the initiator and target service name respectively. Please note that the name of the target service is specified as a string literal. Then with the ON CONTRACT clause, you specify the contract, which will be used to validate the messages being sent in this conversation. RELATED_CONVERSATION and RELATED_CONVERSATION_GROUP are somewhat the same and used to combine related conversations together to form a conversation group. The LIFETIME clause allows you to specify the maximum number of seconds a conversation can exist. For your conversation to be successfully completed it must be ended by both end-points within the specified number of seconds or else an error is returned and rollback is done. If you are sending messages across SQL Server instances, you can set ENCRYPTION = ON to encrypt the messages. Please note encryption does not happen inside a SQL Server instance. I will talk more about it in later article on SSBS security.

Example – The statement below will start a dialog conversation between SSBSLearning/ProductStockStatusCheck/InitiatorService and SSBSLearning/ProductStockStatusCheck/TargetService services and will validate the messages of this conversation with SSBSLearning/ProductStockStatusCheckContract contract. Please note the dialog conversation handle is being returned in @SSBSInitiatorDialogHandle variable, which is of type uniqueidentifier.

BEGIN DIALOG @SSBSInitiatorDialogHandle


SEND and RECEIVE are two new T-SQL commands for putting messages into the queue and retrieving them from the queue. The SEND command is used to send and transfer the messages to the target queue. To send a message you need a dialog conversation handle, which you created in the above example with the BEGIN DIALOG command, to send messages in that particular conversation. Additional information about the SEND command can be found here.

Example – The statement below will send a message on the provided conversation handle.

DECLARE @RequestMessage XML;  
SELECT @RequestMessage =

The RECEIVE command reads messages from a queue and returns a result set for processing. You can use the where clause to filter out the result set but only on the basis of conversation_handle or conversation_group_id. The WAITFOR command has been extended to be used with the RECEIVE command and to have a TIMEOUT clause, which specifies to wait for a message to arrive in the queue for the given period of time in milliseconds if the result set is not available. Once you are done with the received message you would usually end the conversation with the END CONVERSATION command. Additional information about RECEIVE command can be found here.

Example – The statement below will retrieve a message for the dbo.SSBSLearningTargetQueue queue. If the result set is not already available then it will wait for 1000 milliseconds.

    @SSBSTargetDialogHandle = conversation_handle,
    @RecvdRequestMessage = CONVERT(XML, message_body),
    @RecvdRequestMessageTypeName = message_type_name
  FROM dbo.SSBSLearningTargetQueue
), TIMEOUT 1000;

When you send a message into a database on the same SQL Server instance, the message is directly put into the target queue whereas if the database is on another SQL Server instance then the message is first kept in the transmission queue (sys.transmission_queue) until it is transmitted to the target queue. However, there might be some situations when the message is put into the transmission queue even if the target queue is in the database on the same SQL Server. This happens if the message cannot be put into the target queue because the target queue is in a disabled state or because of any other errors; in that case the message is stored in the transmission queue until its delivery.


As long as you have an Initiator and Target, either in same database or in different databases on the same instance, you would not be creating a route as there is a default “AutoCreatedLocal” route in each database, which matches the service name and ensure messages are delivered within the SQL instance.

A route is created explicitly if the Initiator and Target are on different SQL Server instances and lets SSBS know where to deliver the messages. In other words, a route is a mapping or a means to locate the target service while sending messages and to locate the initiating service while sending the response back. You create a route using the CREATE ROUTE command and bind it with the Service; along with that you also specify the ServerName:PortNo with the Address clause of this command. I will be talking about thiks in detail when I will demonstrate writing SSBS applications across SQL Server instances.

So far, I talked about the different components of SSBS individually. This diagram depicts how these components are related to each other and how they interact with each other. A service is bonded with a queue, a queue can have messages conforming to one or more contracts, and likewise a contract can have one or more message types within it.

how the SSBS components are related to each other


In my last article (SQL Server Service Broker Components - Message Types, Contracts and Queue), I discussed a few of the components of SSBS; for example Initiator, Target, Message Types, Contract and Queue. This article has covered the remaining SSBS components; for example Service, Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components are related to each other. In the next couple of articles, I will be helping you to write SSBS applications.

Additional Resources

Getting Started with SQL Server Service Broker
MSDN SQL Server Service Broker
MSDN Creating Service Broker Objects
MSDN Creating Service Broker Message Types
MSDN Contracts
MSDN: Microsoft Message Queuing

» See All Articles by Columnist Arshad Ali

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.