25 - Implementing Service Broker

25.1 Creating Messages

A message is an entity exchanged between the Service Broker services. A message requires the name for participating in the conversation. The validation over the data type is contained in the message.

The CREATE MESSAGE TYPE statement creates a new message. The following is the syntax for createing message:

CREATE MESSAGE TYPE message_type_name
WITH SCHEMA COLLECTION schema_collection_name } ] [ ; ]


message_type_name is the name of the message type that user wants to create
VALIDATION specifies the way the  message should be validated
NONE It specifies that no validation is performed
EMPTY It specifies the message body must be NULL
WELL_FORMED_XML It specifies that the message must be well formatted
VALID_XML WITH SCHEMA COLLECTION It is the message present in the XML snippet

The following example creates a message type as sendMsg.


In the above code, a message type as sendMsg stating the message is well formed XML snippet.

25.2 Creating Queues

A queue is an object used to store messages in an application. It is used as a storage medium between two services. It is used as pipeline for messages. When the queue receives a message, it calls stored procedure to process the message.

The CREATE QUEUE statement is used to create the queue. The syntax for creating the queue is as shown below:

CREATE QUEUE [ database_name. [ schema_name. ] ] queue_name
[ WITH [ STATUS = { ON | OFF } [ , ] RETENTION = { ON | OFF } [ ] ]
<procedure>, MAX_QUEUE_READERS = max_readers, EXECUTE AS
{ SELF | [ ON { filegroup | DEFAULT ] } ]


queue_name is the name of the queue

STATUS ( Queue ) specifies the state of the queue. The value can be ON or OFF.

RETENTION specifies whether the messages sent or received within conversation should be retained in the queue or not

ACTIVATION is the information about the stored procedure that processes the message 
STATUS ( Activation ) specifies whether the queue should execute the stored procedure

PROCEDURE_NAME = <procedure> specifies the name of the stored procedure

MAX_QUEUE_READERS is the maximum number of instances of the stored procedure

EXECUTE AS is the user account under which the procedure is stored

The following example is used to create a queue as sendQueue.

        PROCEDURE_NAME = sendProc,
        MAX_QUEUE_READERS = 10,


In the above code, the queue as sendQueue is used to receive messages. The procedure as sendProc is used for saving the messages. The permission of the current user is used. The queue starts as ten instances of the stored procedure.

The following code is used to show that the queue is unavailable for receiving messages.


25.3 Creating Contracts

A contract is an agreement between two services needed for communication with each other. The type of message used for communication is specified in the contract. The CREATE CONTRACT statement is used for creating the contract.

    CREATE CONTRACT contract_name
    [ AUTHORIZATION owner_name ]
    ( { { message_type_name | DEFAULT ] }
    SENT BY { INITIATOR | TARGET | ANY } } [ ,…n ] ) [ ; ]

Where ,
contract_name is the name of the contract
AUTHORIZATION owner_name is the owner of the contract
message_type_name is the name of the message type to be added
SENT BY is the type of the message send to the endpoint. It has the values that are specified in the syntax
The following statement creates a contract as sendContract.

    CREATE CONTRACT sendContract 
    sendMessage SENT BY INITIATOR

The contract as sendContract is created. The sendMessage is used for the communication.

25.4 Creating Services

A service is used by the Service Broker to send the messages to the correct queue in the database. It is used to route the messages to another database.

   CREATE SERVICE service_name
   [ AUTHORIZATION owner_name ] ON
   [ schema_name. ] queue_name
   [ ( contract_name | [ DEFAULT ] [ ,…n ] )
   ] [ ; ]


service_name is the name of the service

ON QUEUE [ schema_name. ] queue_name is the queue in which the messages are sent
contract_name is the contract for which the service targets

The following statement creates a service as sendService on the queue.

    CREATE SERVICE sendService
    [dbo]. [sendQueue]

In the Service Broker, the services communicate with each other using a dialog. A dialog allows user for bi-directional communication between the two services. The order of the sent and received messages is maintained. The dialog conversation between the services must be initiated before the messages are started.

The BEGIN DIALOG statement is used to begin the conversation. The syntax for BEGIN DIALOG is as shown below:

      @dialog_handle FROM SERVICE
       [ ‘target_service_name’ ] [ ON CONTRACT  contract_name ]


@dialog_handle is the variable name for storing the handle
FROM SERVICE initiator_service_name is the service that initiates the dialog
TO SERVICE ‘target_service_name’ is the target service to initiate the dialog
ON CONTRACT contract_name is the name of the contract

The following statements are used to begin conversation between the sendService and receiveService services.

    SERVICE [ sendService ] TO SERVICE 'receiveService' 
    ON CONTRACT [ sendContract ] ;


In the above statement, the begin dialog conversation and stores the identifier for the variable. The sendService is the initiator for the dialog, and the receiveService is the target of the dialog. The sendContract is the name of the contract.

25.6 Sending and Receiving Messages

Once user has created all the broker objects, started the conversation, they are ready to send and receive messages.

The SEND statement is used to send the messages. The syntax for SEND statement is as shown below:

   SEND ON CONVERSATION conversation_handle
   [ MESSAGE TYPE message_type_name ]
   [ ( meesage_body_expression ) ] [ ; ]


ON CONVERSATION conversation_handle is the conversation to which the message belongs
MESSAGE TYPE message_type_name is the message type of the sent message

Consider the example of send message as shown below:

   @dialog_handle MESSAGE TYPE [ sendMessage ]
   ( ‘<name>Mark</name>’ )

To receive the message use the RECEIVE statement and follows the steps as shown below:

1) User must declare the variables for storing the details of the message

2) Call the RECEIVE statement

3) Process the message

4) The END CONVERSATION statement is used to end the conversation

The syntax for receive statement is as shown below:

        RECEIVE [ TOP ( n ) ]
        < column_specifier > [ ,….n ]
        FROM queue_name
() ] [ , TIMEOUT timeout ]


WAITFOR is used to specify that the RECEIVE statement waits for the message to arrive on the queue

TOP ( n ) specifies the messages to receive from the queue

column_specifier [ …n ] specifies the list of columns user wants to retrieve

queue_name is the name of the queue

TIMEOUT timeout is the amount of time for the statement to wait for the message

The following example is used to demonstrate the receive message from queue.

DECLARE @message_body nvarchar (200)
        RECEIVE TOP (1)
        FROM EmployeeData
           ), TIMEOUT 4000

The above statement receives one message from the queue as EmployeeData and stores in the variable as @message_body. The processing will wait for 3 seconds for the message to arrive in the queue.

The END CONVERATION statement is used to end the conversation. The syntax for the END CONVERSATION is as shown below:

       END CONVERSATION conversation_handle


conversation_handle is the conversation handle for the conversation to end

The following example is used to end the conversation.

       END CONVERSATION @dialog_handle

Like us on Facebook