Microsoft® SQL Server® 2012 Bible

653

Chapter 25: Asynchronous Messaging with Service Broker

25

Service Broker is not a trigger that can code when a message is placed on the queue; some

code must run to extract the message. To accomplish this, you use the waitfor command,

enabling it to wait for a message to be placed in the queue. Without this option, the code

would need to run a loop to continuously check for a new message.

You may want to wait for messages with the waitfor command. The following examples

show how to wait for a specifi c time, a specifi c time period, and a specifi c queue:

--Wait until 9:00 PM

WAITFOR TIME '21:00'

--Wait for 22 Minutes to pass

WAITFOR DELAY '00:22:00'

--Wait for a message to be received from a Service Broker messaging

queue

WAITFOR (RECEIVE * FROMTargetQueue)

The following routine within a stored procedure waits for a message and then receives the

top message from the queue:

USE AdventureWorks2012 ;

GO

-- Process all conversation groups.

WHILE (1 = 1)

BEGIN

DECLARE @conversation_handle UNIQUEIDENTIFIER,

@conversation_group_id UNIQUEIDENTIFIER,

@message_body XML,

@message_type_name NVARCHAR(128);

BEGIN TRANSACTION ;

-- Get next conversation group.

WAITFOR(

GET CONVERSATION GROUP @conversation_group_id

FROM [dbo].[TargetQueue]),

TIMEOUT 500 ;

-- If there are no more conversation groups, roll back the

-- transaction and break out of the outermost WHILE loop.

IF @conversation_group_id IS NULL

BEGIN

ROLLBACK TRANSACTION ;

c25.indd 653c25.indd 653 7/31/2012 9:21:38 AM7/31/2012 9:21:38 AM

http://www.it-ebooks.info