SQL Server 2005 Express Edition - Part 29 - Implementing Service Broker Conversation

August 25, 2008

In the recent installments of our series covering features available in SQL Server 2005 Express Edition, we have been discussing functionality incorporated into asynchronous messaging framework of Service Broker. So far, we have examined its basic characteristics and presented initial steps of a sample implementation intended to demonstrate a message-based communication, which involved creation of required database objects, including message types, contracts, queues, and services associated with them. We also have described how to initiate a dialog between two services residing in the same database and exchange messages between them. In this article, we will focus on the process of ending an existing conversation.

Once a dialog is established, it can be used to communicate between an initiator and a target. Note that at this point, these terms are no longer relevant, since either party is capable of generating and accepting messages. Similarly, each one is permitted to terminate a conversation by launching an END CONVERSATION T-SQL statement followed by an appropriate handle identifier, whose value can be obtained by querying the sys.conversation_endpoints view (listing all conversations in which services hosted by the current database participate). For the purpose of our example, we will locate it by searching for an entry with the state of CO (indicating conversation in progress) and //databaseJournal.com/SQL2005EX/ServiceBroker/svcSend in the far_service column:

DECLARE @convHandle uniqueidentifier
SELECT  @convHandle = conversation_handle 
 FROM sys.conversation_endpoints
 WHERE state = 'CO' AND 
 far_service = 
        '//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend';
END CONVERSATION @convHandle;

Note that the END CONVERSATION statement is typically combined with (and enclosed in the same transaction as) the RECEIVE statement we executed earlier, eliminating the need for extracting conversation handle in this manner (since the appropriate handle becomes readily available when a message is received from the queue), as in the following code (which, for clarity sake, contains only the relevant - in this context - portions of our original T-SQL statement):

WAITFOR(RECEIVE TOP(1)
 @convHandle = conversation_handle
-- (...)
 FROM qRecv)
-- (...);
END CONVERSATION @convHandle;

Ending a conversation using this approach automatically deletes any associated messages remaining in the local queue and sends a system message with message_type_id of 2 (corresponding to the message_type_name of http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog) to its partner, indicating that no more communication will be accepted as part of the same conversation. (In order to remove all messages associated with a specific dialog without sending a message, add the WITH CLEANUP clause to the END CONVERSATION @convHandle statement). You can verify the outcome of this process by examining the content of the initiator and target queues, as well as the sys.transmission_queue view:

SELECT * FROM qSend WITH (NOLOCK);
SELECT * FROM qRecv WITH (NOLOCK);
SELECT * FROM sys.transmission_queue;

You should notice that the state of the entry sys.transmission_queue corresponding to the conversation with far_service of //databaseJournal.com/SQL2005EX/ServiceBroker/svcSend has changed to DI (which designates DISCONNECTED_INBOUND). At this point, we can capture (and store in the tbMsgs table) the system message with message_type_id of 2 delivered to our qSend queue, using the same set of T-SQL statements we applied before to the content of qRecv queue and issue the END CONVERSATION statement to close the dialog (which should be reflected by the change of state column to CD (indicating CLOSED) listed in the sys.conversation_endpoints view.

DECLARE @convHandle UNIQUEIDENTIFIER;
DECLARE @msgTypeName SYSNAME;
DECLARE @status TINYINT;
DECLARE @srvName NVARCHAR(512);
DECLARE @srvConName NVARCHAR(256);
DECLARE @msgTypeValidation AS NCHAR(2);
DECLARE @msgBody NVARCHAR(50);
DECLARE @cmd AS NVARCHAR(50);
WAITFOR(RECEIVE TOP(1)
 @convHandle = conversation_handle,
 @msgTypeName =  message_type_name,
 @status = status,
 @srvName = service_name,
 @srvConName = service_contract_name,
 @msgTypeValidation = validation,
 @msgBody = CAST(message_body AS NVARCHAR(50))
 FROM qSend), 
 TIMEOUT 1000;
IF(@@rowcount != 0)
  BEGIN
 INSERT INTO 
  tbMsgs(convHandle, msgTypeName, status, srvName, srvConName, msgTypeValidation, msgBody)
  VALUES(@convHandle, @msgTypeName, @status, @srvName, @srvConName, @msgTypeValidation, @msgbody)
 END CONVERSATION @convHandle; 
  END

To confirm the outcome, query the content of our table (which at this point should contain two entries, one corresponding to our message delivered to the qRecv queue, and the other representing the system message generated by the END CONVERSATION statement issued by the target service), the initiator and target queues, as well as sys.transmission_queue and sys.conversation_endpoints views by running:

SELECT * FROM tbMsgs;
SELECT * FROM qSend WITH (NOLOCK);
SELECT * FROM qRecv WITH (NOLOCK);
SELECT * FROM sys.transmission_queue;
SELECT * FROM sys.conversation_endpoints;

In the next article of our series, we will present the steps required to reproduce an equivalent conversation in a distributed environment, with initiator and target services residing in databases hosted on two separate instances of a database engine. This will also provide an opportunity to point out some of the unique characteristics of Service Broker implementation in SQL Server 2005 Express Edition.

» See All Articles by Columnist Marcin Policht