Azure Service Bus listener

Show allHide all

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

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;