tailieunhanh - Microsoft SQL Server 2005 Developer’s Guide- Part 16

Microsoft SQL Server 2005 Developer’s Guide- P16:SQL Server 2005 is a feature-rich release that provides a host of new tools and technologies for the database developer. This book is written to help database developers and DBAs become productive immediately with the new features and capabilities found in SQL Server 2005. | Chapter 4 SQL Server Service Broker 129 the network they recommend using names like MySample SenderService . The ON CONTRACT keyword specifies the contract that s used for the dialog. The Contract specifies the contract that will be used. Then a SEND operation is executed to send a message on the conservation that was started. Finally the transaction is committed. The target service will receive the message and add it to the queue that is associated with that service. At this point you can see the message on the ReceiverQueue by running the following SELECT command USE AdventureWorks GO SELECT FROM ReceiverQueue This shows two entries in the ReceiverQueue. The first entry on the queue is for the message that was placed on the queue by the sample application and the second entry was created by the END CONVERSATION command. A partial view of the result set showing the contents of the ReceiverQueue is shown here status priority queuing_order conversation_group_id 1 0 0 82C5F460-3305-DA11-8D17-005056C00008 1 0 1 82C5F460-3305-DA11-8D17-005056C00008 2 row s affected In order to see the contents of the message you need to cast the contents of the message_body column in the results set to a varchar as is shown in the following listing USE AdventureWorks GO SELECT CAST message_body as nvarchar MAX from ReceiverQueue The result set showing the contents of the message is listed here message Service Broker is Cool message NULL 2 row s affected 130 Microsoft SQL Server 2005 Developer s Guide Retrieving Messages from a Queue Now that you ve seen how to add a message to a queue the next example will illustrate how to retrieve the messages off the queue. You can see the T-SQL code in the following listing use Adventureworks GO DECLARE conversation UNIQUEIDENTIFIER DECLARE message_type_id int DECLARE message_body NVARCHAR 1000 DECLARE message NVARCHAR 1000 while 1 1 BEGIN BEGIN TRANSACTION WAITFOR RECEIVE top 1 message_type_id message_type_id message_body .