Learn Sql Server   Learn Sql Theory   Learn Sql

Chapter 1
Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10

Service Broker

Creating Basic Service Broker Application

Creating the Database Master Key for Encryption

Create Message Type

Sql Sever Create Contract

Create Queue

Create Service

Sql Server Begin Dialog Conversation

Querying the Queue for Incoming Messages

RECEIVE statement

End Conversation

Remote-Server Service Broker Implementations

Service Broker Transport Security

Service Broker Enable Dialog Security

Creating Routes and Remote Service Bindings

Create Event Notifications

Configuring a Message Queue

Working with Dialogs using Service Broker

Create Remote Service Binding

Creating Service Broker Message Types


Changing SQL Server Configurations

Sql Server Creating and Configuring Databases

Using sp_helpdb to Viewing Database Information

SQL Create Database Statement

SQL Create Table Statement

Creating a Database Using File Options

Creating Database with User-Defined Filegroup

Setting Database User Access

Sql Server Renaming a Database

Dropping a Database

Detaching Database

Sql Server Attach Database

Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

Begin Dialog Conversation in Sql Server

A dialog conversation is the act of exchanging messages between services. A new conversation is created using the BEGIN DIALOG CONVERSATION. Each new conversation generates a unique conversation handle of the uniqueidentifier data type.


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

Argument Description
@dialog_handle The uniqueidentifier data type local variable that is used to hold the new dialog handle.
initiator_service_name The service that initiates the conversation.
'target_service_name' The target service that the initiating service will exchange messages with.
'service_broker_guid' | 'CURRENT DATABASE' The service_broker_guid as retrieved for the target service database from sys.databases. If CURRENT DATABASE is designated, the service_broker_guid is used from the current database.
contract_name The name of the contract that the conversation is based on.
related_conversation_handle The uniqueidentifier value of the existing conversation group that the dialog belongs to.
related_conversation_group_id The uniqueidentifier value of the existing conversation group that the new dialog is added to.
dialog_lifetime The number of seconds that the dialog is kept open.
ENCRYPTION = { ON | OFF } When set to ON, encryption is used for messages sent outside of the initiator SQL Server instance.

The END CONVERSATION command finishes one side of the conversation. Messages can no longer be sent or received for the service that ends the conversation. Both services (initiator and target) must end the conversation in order for it to be completed.

The SEND command is used to send a message on a specific open conversation. In this command, the message type and message contents are also defined.

Continuing with the online bookstore example, and with the required objects established, you are now ready to initiate a dialog between the two Service Broker services.

On the BookStore database, the following commands are executed in a batch:

Use BookStore
DECLARE @Conv_Handler uniqueidentifier
DECLARE @OrderMsg xml;

FROM SERVICE [//SackConsulting/BookOrderService]
TO SERVICE '//SackConsulting/BookDistributionService'
ON CONTRACT [//SackConsulting/BookOrderContract];

SET @OrderMsg =


MESSAGE TYPE [//SackConsulting/SendBookOrder]

How It Works

In the previous batch of statements, two local variables were used to hold the dialog conversation handle and the order message XML document:

DECLARE @Conv_Handler uniqueidentifier
DECLARE @OrderMsg xml;

The BEGIN DIALOG CONVERSATION command was used to create a conversation between the two services, based on the established contract. The first argument passed was the @Conv_Handler local variable:


The initiator used to begin the dialog was designated in the second line and the target service in the third:

FROM SERVICE [//SackConsulting/BookOrderService]
TO SERVICE '//SackConsulting/BookDistributionService'

The contract name was then designated:

ON CONTRACT [//SackConsulting/BookOrderContract];

The @OrderMsg local variable was set to an XML document containing order and line item information:

SET @OrderMsg =


The SEND ON CONVERSATION command used the conversation handler local variable to send a message using the specified (and allowed) message type and the actual XML message content. The first argument in the command was the @Conv_Handler value populated from the BEGIN DIALOG CONVERSATION command:


The second argument was the message type to be used, followed by the XML message in the local variable:

MESSAGE TYPE [//SackConsulting/SendBookOrder]

This message was then sent to the queue in the BookDistribution database.

Suggested topic

Begin Begin End

Begin Begin Dialog Conversation

End End Conversation

Service Service Broker Enable Dialog Security

Working Working with Dialogs Service Broker

Sponsored Links