SQL Server: CREATE QUEUE Function

DBA/Developer authored the following queue statement

CREATE QUEUE MyQueue
WITH STATUS = ON,
ACTIVATION
(STATUS = ON,
PROCEDURE_NAME = ReceiveData,
MAX_QUEUE_READERS = 3,
EXECUTE AS SELF);

Before creating a service, DBA/Developer must execute the CREATE QUEUE statement to create a queue. A queue stores the incoming message for a service. Each service must be associated with a queue. Therefore, DBA/Developer must create a queue using the CREATE QUEUE statement before creating a service. The syntax of the CREATE QUEUE statement is as follows:

CREATE QUEUE queue_name
[WITH [STATUS = {ON | OFF}],
[RETENTION = {ON |
OFF}],
[ACTIVATION ([STATUS = {ON | OFF},]
PROCEDURE_NAME = procedure_name,
MAX_QUEUE_READERS = max_num_readers,
EXECUTE AS {SELF | 'user_name' | OWNER})]]
[ON {filegroup | [DEFAULT]}];

The clauses of the CREATE QUEUE statement are as follows:

WITH STATUS: Specifies the status of the queue that indicates whether the queue receives messages. With STATUS=ON, the queue receives the messages.

RETENTION: Specifies whether the sent and received messages will be retained in the queue until conversations have ended.

ACTIVATION: Specifies information about the stored procedure that is activated to process the message. The ACTIVATION clause can specify a STATUS option to indicate if the stored procedure immediately processes the message, and a PROCEDURE_NAME option with the name of the stored procedure that should process the message. The ACTIVATION clause can also specify the security context under which the stored procedure should execute using the

EXECUTE AS clause, and the maximum number of instances of the stored procedure that can be concurrently used when processing messages. In this scenario, DBA/Developer should create a queue and specify an ACTIVATION clause in the CREATE QUEUE statement. The ACTIVATION clause allows DBA/Developer to specify the stored procedure that will process incoming messages, and how it will behave. In the ACTIVATION clause, DBA/Developer should specify a PROCEDURE_NAME of ReceiveData to identify the procedure that will process incoming messages, and a STATUS of ON to indicate that the stored procedure should process the messages as soon as they are received. Finally, DBA/Developer should specify EXECUTE AS SELF to indicate that the stored procedure should execute under the security context of the current user. DBA/Developer should not use the statement that specifies WITH STATUS=OFF when creating the queue. This setting indicates that the queue cannot receive messages. DBA/Developer should not use the statement that specifies STATUS=OFF in the ACTIVATION clause of the CREATE QUEUE statement. With this setting, Service Broker will receive each message but will not process it immediately. DBA/Developer should not use the statement that specifies EXECUTE AS OWNER in the ACTIVATION clause of the CREATE QUEUE statement. This would cause the stored procedure to execute under the security context of the queue owner, not of the current user as required in this scenario.