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

In the most recent installments of our series covering functionality
included in the SQL Server 2005 Express Edition, we have started an overview of
Service Broker – asynchronous communication framework incorporated into the
database engine. So far, we have presented its basic concepts and features, as
well as stepped through creation of database objects required to demonstrate
their characteristics (note that for the time being, we are limiting scope of
our discussion to a single database implementation, leaving more complex
scenarios, such as message routing, for later). These steps consisted of
defining a message type (labeled, in our example, //databaseJournal.com/SQL2005EX/ServiceBroker/msgNV) and
associated contract (which we named //databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV),
two queues (qSend and qRecv) and their respective services (//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend
and //databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv),
as well as an auxiliary table (which, while not necessary to establish a
conversation between them, will help us in tracking its progress). In this
article, we will initiate a dialog, send a sample message from qSend to qRecv, process it (following a successful delivery) using
a T-SQL RECEIVE statement,
and observe how our database objects change as the result of these events.

In order to launch a conversation, we will use BEGIN DIALOG CONVERSATION T-SQL statement, which
references an initiator and target (appearing in the FROM SERVICE and TO SERVICE clauses, respectively), the
contract they are obligated to follow (as specified by the ON CONTRACT clause), encryption state
(turned ON or OFF), and an actual message (which
should comply with an earlier designated message type). Optionally, you can
also set a limit for the dialog duration using LIFETIME parameter (expressed in seconds), after which an
error message would be added to both queues, preventing any further
communication. Our dialog, however, will remain open until invocation of the END CONVERSATION @convHandle statement,
which will be issued once successful delivery and completion of message
processing are verified:

DECLARE @convHandle uniqueidentifier
FROM SERVICE [//databaseJournal.com/SQL2005EX/ServiceBroker/svcSend]
TO SERVICE ‘//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv’
ON CONTRACT [//databaseJournal.com/SQL2005EX/ServiceBroker/contAnymsgNV]

At this point, we are ready to send a message as part of the established
dialog. This is accomplished by launching SEND
T-SQL statement with ON CONVERSATION
clause, which includes a parameter referencing the conversation handle. Its
value can be obtained by querying the sys.conversation_endpoints
view, which keeps a record of Service Broker endpoints in the current database
for all of its conversations. While you can use various criteria to identify
the one we just initiated, we will rely on the state and far_service
columns, which should be equal to SO
(started outbound) and //databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv
respectively (for more information about the structure of sys.conversation_endpoints, refer to the
SQL Server 2005
Books Online

DECLARE @convHandle uniqueidentifier
SELECT @convHandle = conversation_handle FROM sys.conversation_endpoints
WHERE state = ‘SO’ AND far_service = ‘//databaseJournal.com/SQL2005EX/ServiceBroker/svcRecv’;
MESSAGE TYPE [//databaseJournal.com/SQL2005EX/ServiceBroker/msgNV] (N’Aloha bruddah’)

The SEND command attempts
to deliver the message directly into the destination queue (since both the
initiator and target are located in the same database), which can be easily
verified by querying its content (using standard SELECT statement). In addition to columns we have already
defined in our tbMsgs table
(which reflect the structure of both queues), you will also notice message_sequence_number (assigned
sequential integer values, starting with 0) indicating the order of a message
within a specific conversation (identified by the conversation_handle) and queuing_order, containing integer value assigned
incrementally to each new message that is being placed in the queue (across all
conversations). If the qRecv
queue turns out to be empty, check the content of the sys.transmission_queue view, which lists
non-delivered messages, making it functionally equivalent to an Outbox feature
in traditional e-mail client software (pay particular attention to the transmission_status column, which
provides the reason for the failure). To obtain this information, execute the

SELECT * FROM sys.transmission_queue;

Now it is time to process the freshly delivered message. This is
accomplished by executing the RECEIVE
statement with the TOP(1)
clause, which extracts it from the qRecv
queue. The retrieved content is used to populate values of variables that will
subsequently be loaded into the tbMsgs
table. We employ the WAITFOR
statement to prevent the RECEIVE
from returning immediately if the queue is empty (which is its default
behavior), keeping it operational for the duration (expressed in milliseconds)
of the TIMEOUT parameter
(its absence results in indefinite wait, ended only by message arrival). Once
the message is processed, it is deleted from the qRecv queue (providing that it arrives before TIMEOUT expires and that our INSERT operation succeeds).

DECLARE @srvConName NVARCHAR(256);
DECLARE @msgTypeValidation AS NCHAR(2);
@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 qRecv),
IF(@@rowcount != 0)
tbMsgs(convHandle, msgTypeName, status, srvName, srvConName, msgTypeValidation, msgBody)
VALUES(@convHandle, @msgTypeName, @status, @srvName, @srvConName, @msgTypeValidation, @msgbody)

If you examine content of the qSend
and qRecv queues at this
point, you will notice that both of them are empty (you can look up the content
of the message we sent earlier by querying the tbMsgs table, where we stored values of most of its
fields). We could continue bidirectional communication between two Service
Broker services, sending and receiving messages using existing conversations
(as long as we reference their handles, which you can extract from the sys.conversation_endpoints system view),
by simply repeating the sequence of steps described above. In the next article
of our series, we will present steps necessary to end this dialog in a graceful


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles