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
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
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
SELECT * FROM qSend WITH (NOLOCK); SELECT * FROM qRecv WITH (NOLOCK); SELECT * FROM sys.transmission_queue;
You should notice that the
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
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.