Comunicación estratégica: internet y redes sociales

*****************************ILLEGAL FOR NON-TRAINER USE******************************

Once you have sent a message, a service program needs to read the message from the queue and process the message body. Precisely when the service program reads the message depends on how you chose to set up the queue. However, the basic steps for receiving a message do not change.

To receive a message, you must perform the following steps: 1. Declare variables for storing message details.

2. Call the RECEIVE statement.

3. Check the message type, and process the message accordingly. 4. If the conversation is at an end, call END CONVERSATION.

You need to create local variables so that you can store individual columns from the message. Common columns that you may want to work with include

conversation_handle, message_type_name, and message_body.

The following example shows the declaration of three local variables that will be used later in this topic.

DECLARE @conversation UNIQUEIDENTIFIER DECLARE @msg NVARCHAR(MAX)

DECLARE @msgType NVARCHAR(256)

Introduction

The RECEIVE statement removes one or more messages from a specified queue so that you can work with the message as you would with any other table-based result set. The statement removes all messages belonging to the same service instance identifier unless you include the TOP parameter in the statement. During this time, no other service program instance can work with the messages you receive or with any other message that has the same service instance identifier. RECEIVE removes messages in order based on the

message_sequence_order value for each message in a conversation.

RECEIVE has the following syntax.

[ WAITFOR ( ]

RECEIVE [ TOP (n) ]

< column_specifier > [ ,...n ] FROM queue_name

[ INTO table_variable ]

[ WHERE { conversation_handle = conversation_handle

| conversation_group_id = conversation_group_id } ] [ ) ] [ , TIMEOUT timeout ]

The RECEIVE syntax is described in the following table:

Parameter Description

WAITFOR Blocks processing of the RECEIVE until a message

arrives in the queue. If you specify a TIMEOUT value in milliseconds, the RECEIVE will wait only for the given length of time before returning an empty result set if no message arrives. Omitting this parameter or setting it to - 1 cases the RECEIVE statement to wait until a message arrives.

TOP Allows you to specify how many messages to receive from the queue. If you do not specify the TOP parameter, you will receive all of the messages that match a single service instance identifier. Often you will specify the value 1 in order to work with individual messages.

column_specifier Used to list the columns that you intend to use in your message processing logic. For a complete list, see the SQL Server 2005 Books Online.

FROM Specifies the name of the queue where you want to check

for messages.

INTO Allows you to store the result set in a table rather than in

local variables. You should use this approach when you want to work with multiple messages simultaneously.

WHERE Allows you to limit the returned messages to a specific

conversation handle or conversation group identifier. You cannot use any other columns in the WHERE clause.

Calling the RECEIVE statement

The following example shows how to receive a single message and store three columns from the message in local variables.

;RECEIVE TOP(1) @conversation = conversation_handle, @msgType = message_type_name, @msg = message_body

FROM ExpenseQueue

You should check @@ROWCOUNT after a RECEIVE statement to ensure that a message was found. If @@ROWCOUNT equals 0 (zero), the queue did not contain any messages.

To ensure that you are dealing with the correct type of message, you can check the message_type_name column from the message result set by using a combination of IF statements. Possible message types include:

! The expected message type.

! An error message of the message type

http://schemas.microsoft.com/SQL/ServiceBroker/Error. ! A dialog timeout message of the message type

http://schemas.Microsoft.com/SQL/ServiceBroker/DialogTimer. ! An end dialog message of the message type

http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog. ! An unknown or unexpected message type.

If the message is not of the expected message type, you can end the

conversation and return an error message to the service consumer by using the

END CONVERSATION statement, as shown in the following example.

IF (@msgType = '//Adventure-Works.com/Expenses/ExpenseClaim') -- process @msg ... ELSE IF (@msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error') OR (@msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') END CONVERSATION @conversation

ELSE

END CONVERSATION @conversation

WITH ERROR = 500 DESCRIPTION = 'Invalid message type.'

The example also checks to see whether the message is an error message or an end dialog message as predefined by Service Broker. In either case, the example simply ends the conversation. Any other message type results in the end of the conversation and an error message being returned to the service consumer.

Another common action when processing a message is to send a new message either in response to the incoming message or on to another service provider. If the new message should be included as part of the same conversation, use the

RELATED_CONVERSATION parameter in the BEGIN DIALOG

CONVERSATION statement by using the current message’s conversation handle.

Tip

Checking the message type and processing the message

If the service provider task is complete, you can end the conversation by using the END CONVERSATION statement. END CONVERSATION has the following syntax.

END CONVERSATION conversation_handle

[ [ WITH ERROR = failure_code DESCRIPTION = failure_text ] | [ WITH CLEANUP ] ]

The conversation_handle is the identifier of the conversation that you want to end. You can specify an optional error code and description using the WITH ERROR clause as shown in the preceding syntax.

The WITH CLEANUP clause removes all messages and metadata for this side of the conversation without notifying the other side of the conversation. SQL Server drops the conversation endpoint, all messages for the conversation in the transmission queue, and all messages for the conversation in the service queue.