Transact-SQL Syntax ConventionsSends a message, using one or more existing conversations.
Transact-SQL Syntax Conventions
SEND
ON CONVERSATION [(]conversation_handle [,.. @conversation_handle_n][)]
[ MESSAGE TYPE message_type_name ]
[ ( message_body_expression ) ]
[ ; ] ON CONVERSATION conversation_handle [.. @conversation_handle_n]
Specifies the conversations that the message belongs to. The conversation_handle must contain a valid conversation identifier. The same conversation handle cannot be used more than once.
MESSAGE TYPE message_type_name
Specifies the message type of the sent message. This message type must be included in the service contracts used by these conversations. These contracts must allow the message type to be sent from this side of the conversation. For example, the target services of the conversations 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]
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 services on one end of one or more Service Broker conversations to the services on the other end of these conversations. The RECEIVE statement is then used to retrieve the sent message from the queues associated with the target services.
The conversation handles supplied to the ON CONVERSATION clause comes from one of three 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 services in other instances of the SQL Server Database Engine are stored in a transmission queue in the current database until they can be transmitted to the service queues in the remote instances. Messages sent to services in the same instance of the SQL Server Database Engine Database Engine are put directly into the queues associated with these services. If a condition prevents a local message from being put directly in the target 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 target service queue being inactive. You can use the sys.transmission_queue system view to see the messages in the transmission queue.
SEND is an atomic statement, that is, if a SEND statement sending a message on multiple conversations fails, e.g. because a conversation is in an errored state, no messages will be stored in the transmission queue or put in any target service queue.
Service Broker optimizes the storage and transmission of messages that are sent on multiple conversations in the same SEND statement.
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 separately locks each conversation on which a message is sent to ensure per-conversation ordered delivery.
SEND is not valid in a user-defined function.
To send a message, the current user must have RECEIVE permission on the queue of every service that sends the message.
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) ; The following example starts three dialogs and sends an XML message on each of them.
DECLARE @dialog_handle1 UNIQUEIDENTIFIER,
@dialog_handle2 UNIQUEIDENTIFIER,
@dialog_handle3 UNIQUEIDENTIFIER,
@OrderMsg XML ;
SET @OrderMsg = < construct message as appropriate for the application > ;
BEGIN DIALOG @dialog_handle1
FROM SERVICE [//InitiatorDB/InitiatorService]
TO SERVICE '//TargetDB1/TargetService’
ON CONTRACT [//AllDBs/OrderProcessing] ;
BEGIN DIALOG @dialog_handle2
FROM SERVICE [//InitiatorDB/InitiatorService]
TO SERVICE '//TargetDB2/TargetService’
ON CONTRACT [//AllDBs/OrderProcessing] ;
BEGIN DIALOG @dialog_handle3
FROM SERVICE [//InitiatorDB/InitiatorService]
TO SERVICE '//TargetDB3/TargetService’
ON CONTRACT [//AllDBs/OrderProcessing] ;
SEND ON CONVERSATION (@dialog_handle1, @dialog_handle2, @dialog_handle3)
MESSAGE TYPE [//AllDBs/OrderMsg]
(@OrderMsg) ; BEGIN DIALOG CONVERSATION (Transact-SQL)
END CONVERSATION (Transact-SQL)
RECEIVE (Transact-SQL)
sys.transmission_queue (Transact-SQL)