Export (0) Print
Expand All

SEND (Transact-SQL)

Sends a message, using an existing conversation.

Topic link icon Transact-SQL Syntax Conventions


SEND
   ON CONVERSATION conversation_handle
   [ MESSAGE TYPE message_type_name ]
   [ ( message_body_expression ) ]
[ ; ]

ON CONVERSATION conversation_handle

Specifies the conversation that the message belongs to. The conversation_handle must contain a valid conversation identifier.

MESSAGE TYPE message_type_name

Specifies the message type of the sent message. This message type must be included in the service contract used by this conversation. The contract must allow the message type to be sent from this side of the conversation. For example, the target of the conversation may only send messages specified in the contract as SENT BY TARGET or SENT BY ANY. If this clause is omitted, the message is of the message type DEFAULT.

message_body_expression

Provides an expression representing the message body. The message_body_expression is optional. However, if the message_body_expression is present the expression must be of a type that can be converted to varbinary(max). The expression cannot be NULL. If this clause is omitted, the message body is empty.

Important noteImportant

If the SEND statement is not the first statement in a batch or stored procedure, the preceding statement must be terminated with a semicolon (;).

The SEND statement transmits a message from the service on one end of a Service Broker conversation to the service on the other end of the conversation. The RECEIVE statement is then used to retrieve the sent message from the queue associated with the destination service.

The conversation handle supplied to the ON CONVERSATION clause comes from one of two sources:

  • When sending a message that is not in response to a message received from another service, use the conversation handle returned from the BEGIN DIALOG statement that created the conversation.

  • When sending a message that is a response to a message previously received from another service, use the conversation handle returned by the RECEIVE statement that returned the original message.

In many cases, the code that contains the SEND statement is separate from the code that contains either the BEGIN DIALOG or RECEIVE statements supplying conversation handle. In these cases, the conversation handle must be one of the data items in the state information passed to the code that contains the SEND statement.

Messages that are sent to a service in another instance of the SQL Server Database Engine are stored in a transmission queue in the current database until they can be transmitted to the service queue in the remote instance. Messages sent to a service in the same instance of the Database Engine are put directly into the queue associated with that service. If a condition prevents a local message from being put directly in the destination service queue, it can be stored in the transmission queue until the condition is resolved. Examples of when this occurs include some types of errors or the destination service queue being inactive. You can use the sys.transmission_queue system view to see the messages in a transmission queue.

Messages in the transmission queues for an instance are transmitted in sequence based on:

  • The priority level of their associated conversation endpoint.

  • Within priority level, their send sequence in the conversation.

Priority levels specified in conversation priorities are only applied to messages in the transmission queue if the HONOR_BROKER_PRIORITY database option is set to ON. If HONOR_BROKER_PRIORITY is set to OFF, all messages put in the transmission queue for that database are assigned the default priority level of 5. Priority levels are not applied to a SEND where the messages are put directly into a service queue in the same instance of the Database Engine.

The SEND statement locks the conversation handle and not the group to which the handle belongs.

SEND is not valid in a user-defined function.

To send a message, the current user must have SEND permission on the service that the dialog is from. If a remote service binding for the service exists, the user specified in that remote service binding must have RECEIVE permissions on the queue for the service that the dialog is from.

The following example starts a dialog and sends an XML message on the dialog. To send the message, the example converts the xml object to varbinary(max).

DECLARE @dialog_handle UNIQUEIDENTIFIER,
        @ExpenseReport XML ;

SET @ExpenseReport = < construct message as appropriate for the application > ;

BEGIN DIALOG @dialog_handle
FROM SERVICE [//Adventure-Works.com/Expenses/ExpenseClient]
TO SERVICE '//Adventure-Works.com/Expenses'
ON CONTRACT [//Adventure-Works.com/Expenses/ExpenseProcessing] ;


SEND ON CONVERSATION @dialog_handle
    MESSAGE TYPE [//Adventure-Works.com/Expenses/SubmitExpense]
    (@ExpenseReport) ;

Community Additions

ADD
Show:
© 2014 Microsoft