Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 5, 2008

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

By Marcin Policht

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 following:

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), 
 TIMEOUT 1000;
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 manner.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM