Azure Service Bus listener

The procedure call when an Azure Service Bus listener receives a message.

A listener must be fast, or Azure will consider it to have timed out. Therefore, if you need to do slow processing of the received data then the listener should save it to an internal queue table for processing by a separate job or event listener.

SQL

SQL Call: Message received (mandatory)

May modify database: Yes

Resultset: Dead-lettering (optional)

Can be used to move messages to the dead-letter queue if they can not be handled by the listener.
Table count: repeated zero or one time
Row count: one or more rows
Columns
DeadLetterReason mandatory string
The reason why the message was moved to the dead-letter queue. Can be null.
MessageId mandatory string
The id of the message to move to the dead-letter queue.

Resultset: #Message

Table count:
Row count: exactly one row
Columns
Body mandatory binary
The body of the message. The data type will be nvarchar if the listener is set to Text mode and varbinary otherwise.
ContentType mandatory string
The content type of the message.
CorrelationId mandatory string
The correlation id for the message.
MessageId mandatory string
The id of the message.
ReplyTo mandatory string
Reply To for the message.
Subject mandatory string
The subject of the message.
To mandatory any
To for the message.

Resultset: #MessageExtraProperties

Table count:
Row count:
Columns
Key mandatory string
Custom property name.
MessageId mandatory string
The id of the message.
Value mandatory sql_variant
Custom property value.

Examples

Azure Service Bus listener

CREATE PROCEDURE Example.ServiceBusListener
AS
BEGIN
	INSERT Example.ServiceBusMessage
	(
		MessageId,
		Subject,
		Body,
		ContentType,
		CorrelationId,
		ReplyTo,
		[To],
		InsertDatetimeUtc
	)
	SELECT
		MessageId,
		Subject,
		Body,
		ContentType,
		CorrelationId,
		ReplyTo,
		[To],
		SYSUTCDATETIME()
	FROM
		#Message;

	INSERT Example.ServiceBusExtraProperty
	(
		MessageId,
		[Key],
		Value
	)
	SELECT
		MessageId,
		[Key],
		Value
	FROM
		#MessageExtraProperties;
END;