RECEIVE (Transact-SQL)

  • Article

Retrieves one or more messages from a queue. Depending on the retention setting for the queue, either removes the message from the queue or updates the status of the message in the queue.

Syntax

[ WAITFOR ( ]
    RECEIVE [ TOP ( n ) ] 
        <column_specifier> [ ,...n ]
        FROM <queue>
        [ INTO table_variable ]
        [ WHERE {  conversation_handle = conversation_handle
                 | conversation_group_id = conversation_group_id } ]
[ ) ] [ , TIMEOUT timeout ]
[ ; ]

<column_specifier> ::=
{    * 
  |  { column_name | [ ] expression } [ [ AS ] column_alias ]
  |  column_alias = expression 
}     [ ,...n ] 

<queue> ::=
{
    [ database_name . [ schema_name ] . | schema_name . ]
        queue_name
}

Arguments

  • WAITFOR
    Specifies that the RECEIVE statement waits for a message to arrive on the queue, if no messages are currently present.
  • TOP( n )
    Specifies the maximum number of messages to be returned. If this clause is not specified, all messages are returned that meet the statement criteria.
  • *
    Specifies that the result set contains all columns in the queue.
  • column_name
    Is the name of a column to include in the result set.
  • expression
    Is a column name, constant, function, or any combination of column names, constants, and functions connected by an operators.
  • column_alias
    Is an alternative name to replace the column name in the result set.
  • FROM
    Specifies the queue containing the messages to retrieve.
  • database_name
    Is the name of the database that contains the queue to receive messages from. When no database_name is provided, defaults to the current database.
  • schema_name
    Is the name of the schema that owns the queue to receive messages from. When no schema_name is provided, defaults to the default schema for the current user.
  • queue_name
    Is the name of the queue to receive messages from.
  • INTO table_variable
    Specifies the table to select into the columns from received messages.
  • WHERE
    Specifies the conversation or conversation group for the received messages. If omitted, returns messages from the next available conversation group.
  • conversation_handle = conversation_handle
    Specifies the conversation for received messages. The conversation_handle provided must be a uniqueidentifer or a type convertible to uniqueidentifier.
  • conversation_group_id = conversation_group_id
    Specifies the conversation group for received messages. The conversation_group_id provided must be a uniqueidentifier or a type convertible to uniqueidentifier.
  • TIMEOUT timeout
    Specifies the amount of time, in milliseconds, for the statement to wait for a message. This clause may only be used with the WAITFOR clause. If this clause is not specified or the timeout is -1, the wait time is unlimited. If the timeout expires, RECEIVE returns an empty result set.

Remarks

Important

If the RECEIVE statement is not the first statement in a batch or stored procedure, the preceding statement must be terminated with a semicolon (;), the Transact-SQL statement terminator.

The RECEIVE statement reads messages from a queue and returns a result set. The result set returned consists of zero or more rows, each of which contains one message. If the INTO clause is not used, and column_specifier does not assign the values to local variables, the statement returns a result set to the calling program.

The RECEIVE statement removes received messages from the queue unless the queue specifies message retention. When the RETENTION setting for the queue is ON, the RECEIVE statement updates the status column to 1 and leaves the messages in the queue. When a transaction that contains a RECEIVE statement rolls back, all changes to the queue within the transaction are also rolled back, returning messages to the queue.

All messages returned by a RECEIVE statement belong the same conversation group. The RECEIVE statement locks the conversation group for the messages returned until the transaction that contains the statement completes. The result set returned by a RECEIVE statement is implicitly ordered. For a given conversation, a RECEIVE statement returns messages with a status of 1 in ascending message_sequence_number order.

The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression. When no WHERE clause is specified, the RECEIVE statement returns messages from the next available conversation group, regardless of any locks that the current connection holds on other conversation groups. When the WHERE clause does not specify a conversation_handle, the RECEIVE statement returns messages from the conversation group regardless of the conversation that each message belongs to. If the conversation handle or conversation group identifier specified in the WHERE clause does not exist, or is not associated with the queue specified, the RECEIVE statement returns an error.

If the queue specified in the RECEIVE statement has the queue status set to OFF, the statement fails with a Transact-SQL error.

When the WAITFOR clause is specified, the statement waits for the timeout specified, or until a result set is available. If the queue is dropped or the status of the queue is set to OFF while the statement is waiting, the statement immediately returns an error. If the RECEIVE statement specifies a conversation group or conversation handle and the service for that conversation is dropped or moved to another queue, the RECEIVE statement reports a Transact-SQL error.

RECEIVE is not valid in a user-defined function.

The following table lists the columns in a queue.

Column name Data type Description

status

tinyint

Status of the message. For messages returned by the RECEIVE command, the status is always 0. Messages in the queue may contain one of the following values:

0=Ready1=Received message2=Not yet complete3=Retained sent message

priority

tinyint

0. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed..

queuing_order

bigint

Message order number within the queue.

conversation_group_id

uniqueidentifier

Identifier for the conversation group that this message belongs to.

conversation_handle

uniqueidentifier

Handle for the conversation that this message is part of.

message_sequence_number

bigint

Sequence number of the message within the conversation.

service_name

nvarchar(512)

Name of the service that the conversation is to.

service_id

int

SQL Server object identifier of the service that the conversation is to.

service_contract_name

nvarchar(256)

Name of the contract that the conversation follows.

service_contract_id

int

SQL Server object identifier of the contract that the conversation follows.

message_type_name

nvarchar(256)

Name of the message type that describes the message.

message_type_id

int

SQL Server object identifier of the message type that describes the message.

validation

nchar(2)

Validation used for the message.

E=EmptyN=NoneX=XML

message_body

varbinary(MAX)

Content of the message.

Permissions

To receive a message, the current user must have RECEIVE permission on the queue.

Examples

A. Receiving all columns for all messages in a conversation group

The following example receives all available messages for the next available conversation group from the ExpenseQueue queue. The statement returns the messages as a result set.

RECEIVE * FROM ExpenseQueue ;

B. Receiving specified columns for all messages in a conversation group

The following example receives all available messages for the next available conversation group from the ExpenseQueue queue. The statement returns the messages as a result set that contains the columns conversation_handle, message_type_name, and message_body.

RECEIVE conversation_handle, message_type_name, message_body
FROM ExpenseQueue ;

C. Receiving the first available message in the queue

The following example receives the first available message from the ExpenseQueue queue as a result set.

RECEIVE TOP (1) * FROM ExpenseQueue ;

D. Receiving all messages for a specified conversation

The following example receives all available messages for the specified conversation from the ExpenseQueue queue as a result set.

DECLARE @conversation_handle UNIQUEIDENTIFIER ;

SET @conversation_handle = <retrieve conversation from database> ;

RECEIVE *
FROM ExpenseQueue
WHERE conversation_handle = @conversation_handle ;

E. Receiving messages for a specified conversation group

The following example receives all available messages for the specified conversation group from the ExpenseQueue queue as a result set.

DECLARE @conversation_group_id UNIQUEIDENTIFIER ;

SET @conversation_group_id = 
    <retrieve conversation group ID from database> ;

RECEIVE *
FROM ExpenseQueue
WHERE conversation_group_id = @conversation_group_id ;

F. Receiving into a table variable

The following example receives all available messages for a specified conversation group from the ExpenseQueue queue into a table variable.

DECLARE @conversation_group_id UNIQUEIDENTIFIER ;

DECLARE @procTable TABLE(
     service_instance_id UNIQUEIDENTIFIER,
     handle UNIQUEIDENTIFIER,
     message_sequence_number BIGINT,
     service_name NVARCHAR(512),
     service_contract_name NVARCHAR(256),
     message_type_name NVARCHAR(256),
     validation NCHAR,
     message_body VARBINARY(MAX)) ;

SET @conversation_group_id = <retrieve conversation group ID from database> ;

RECEIVE TOP (1)
    conversation_group_id,
    conversation_handle,
    message_sequence_number,
    service_name,
    service_contract_name,
    message_type_name,
    validation,
    message_body
FROM ExpenseQueue
INTO @procTable
WHERE conversation_group_id = @conversation_group_id ;

G. Receiving messages and waiting indefinitely

The following example receives all available messages for the next available conversation group in the ExpenseQueue queue. The statement waits until at least one message becomes available then returns a result set containing all message columns.

WAITFOR (
    RECEIVE *
    FROM ExpenseQueue) ;

H. Receiving messages and waiting for a specified interval

The following example receives all available messages for the next available conversation group in the ExpenseQueue queue. The statement waits for 60 seconds or until at least one message becomes available, whichever happens first. The statement returns a result set containing all message columns if at least one message is available, otherwise the statement returns an empty result set.

WAITFOR (
    RECEIVE *
    FROM ExpenseQueue ),
TIMEOUT 60000 ;

I. Receiving messages, modifying the type of a column

The following example receives all available messages for the next available conversation group in the ExpenseQueue queue. When the message type states that the message contains an XML document, the statement converts the message body to XML.

WAITFOR (
    RECEIVE message_type_name,
        CASE
            WHEN validation = 'X' THEN CAST(message_body as XML)
            ELSE NULL
         END AS message_body 
         FROM ExpenseQueue ),
TIMEOUT 60000 ;

J. Receiving a message, extracting data from the message body, retrieving conversation state

The following example receives the next available message for the next available conversation group in the ExpenseQueue queue. When the message is of type //Adventure-Works.com/Expenses/SubmitExpense, the statement extracts the employee ID and a list of items from the message body. The statement also retrieves state for the conversation from the ConversationState table.

WAITFOR(
    RECEIVE 
    TOP(1)
      message_type_name,
      COALESCE(
           (SELECT TOP(1) ConversationState
            FROM CurrentConversations AS cc
            WHERE cc.ConversationHandle = conversation_handle),
           'NEW')
      AS ConversationState,
      COALESCE(
          (SELECT TOP(1) ErrorCount
           FROM CurrentConversations AS cc
           WHERE cc.ConversationHandle = conversation_handle), 
           0)
      AS ConversationErrors,
      CASE WHEN message_type_name = N'//Adventure-Works.com/Expenses/SubmitExpense'
          THEN CAST(message_body AS XML).value(
                'declare namespace rpt = "http://Adventure-Works.com/schemas/expenseReport"
                   (/rpt:ExpenseReport/rpt:EmployeeID)[1]', 'nvarchar(20)')
         ELSE NULL
      END AS EmployeeID,
      CASE WHEN message_type_name = N'//Adventure-Works.com/Expenses/SubmitExpense'
          THEN CAST(message_body AS XML).query(
                'declare namespace rpt = "http://Adventure-Works.com/schemas/expenseReport" 
                     /rpt:ExpenseReport/rpt:ItemDetail')
          ELSE NULL
      END AS ItemList
    FROM ExpenseQueue 
), TIMEOUT 60000 ;

See Also

Reference

BEGIN DIALOG CONVERSATION (Transact-SQL)
BEGIN CONVERSATION TIMER (Transact-SQL)
END CONVERSATION (Transact-SQL)
SEND (Transact-SQL)
CREATE QUEUE (Transact-SQL)
ALTER QUEUE (Transact-SQL)
DROP QUEUE (Transact-SQL)

Other Resources

Service Broker Tutorials
Conversation Group Locks
Conversation Architecture

Help and Information

Getting SQL Server 2005 Assistance