You are a database developer on an instance of SQL Server 2008. You are creating a Service Broker application. You have created the Message1 and Message2 message types with the required validation. You create a contract using the following statement:
CREATE CONTRACT MyContract (Message1 SENT BY INITIATOR,Message2 SENT BY TARGET);
You want to create a queue to be used by a Service Broker service. The solution should meet the following requirements:
You want the queue to be able to receive messages.
You want all messages received on the queue to be immediately processed by the ReceiveData stored procedure.
You want the ReceiveData stored procedure to execute under the security context of the current user.
Which Transact-SQL statement should you use to create the queue?
A. CREATE QUEUE MyQueue
WITH STATUS=OFF,ACTIVATION (STATUS=ON,PROCEDURE_NAME=ReceiveData,MAX_QUEUE_READERS=3,EXECUTE AS SELF);
B. CREATE QUEUE MyQueue
WITH STATUS=ON,ACTIVATION (STATUS=ON,PROCEDURE_NAME=ReceiveData,MAX_QUEUE_READERS=3, EXECUTE AS OWNER);
C. CREATE QUEUE MyQueue
WITH STATUS=ON,ACTIVATION (STATUS=OFF,PROCEDURE_NAME=ReceiveData,MAX_QUEUE_READERS=3,EXECUTE AS SELF);
D. CREATE QUEUE MyQueue
WITH STATUS=ON,ACTIVATION (STATUS=ON,PROCEDURE_NAME=ReceiveData,MAX_QUEUE_READERS=3,EXECUTE AS SELF);
Correct Answer: D
Explanation/Reference:
You should use the following Transact-SQL statement to create the queue:
CREATE QUEUE MyQueueWITH STATUS=ON,ACTIVATION (STATUS=ON,PROCEDURE_NAME=ReceiveData,MAX_QUEUE_READERS=3,EXECUTE AS SELF);
Before creating a service, you 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, you 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 whether the queue is available (ON) or unavailable (OFF). When the queue is unavailable, no messages can be added to the queue or removed from the queue. You can create the queue in an unavailable state to keep messages from arriving on the queue until the queue is made available with an ALTER QUEUE statement. If this clause is omitted, the default is ON, and the queue is available.
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 which specifies whether Service Broker starts the stored procedure. When STATUS = ON, the queue starts the stored procedure specified with PROCEDURE_NAME when the number of procedures currently running is less than MAX_QUEUE_READERS and when messages arrive on the queue faster than the stored procedures receive messages. When STATUS = OFF, the queue does not start the stored procedure. If this clause is not specified, the default is ON.
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, you should create a queue and specify an ACTIVATION clause in the CREATE QUEUE statement. The ACTIVATION clause allows you to specify the stored procedure that will process incoming messages, and how it will behave. In the ACTIVATION clause, you 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, you should specify EXECUTE AS SELF to indicate that the stored procedure should execute under the security context of the current user.
You should not use the statement that specifies WITH STATUS=OFF when creating the queue. This setting indicates that the queue cannot receive messages.
You 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.
You 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.
