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

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