ALTER QUEUE (Transact-SQL)

Article
12/03/2008

Changes the properties of a queue.

Topic link icon Transact-SQL Syntax Conventions

Syntax

ALTER QUEUE <object> WITH
   [ STATUS = { ON | OFF } [ , ] ]
   [ RETENTION = { ON | OFF } [ , ] ]
   [ ACTIVATION (
       { [ STATUS = { ON | OFF } [ , ] ] 
         [ PROCEDURE_NAME = <procedure> [ , ] ]
         [ MAX_QUEUE_READERS = max_readers [ , ] ]
         [ EXECUTE AS { SELF | 'user_name'  | OWNER } ]
       |  DROP }
          ) ] 
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        queue_name
} 

<procedure> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
        stored_procedure_name
}

Arguments

database_name(object)
Is the name of the database that contains the queue to be changed. When no database_name is provided, this defaults to the current database.

schema_name (object)
Is the name of the schema to which the new queue belongs. When no schema_name is provided, this defaults to the default schema for the current user.

queue_name
Is the name of the queue to be changed.

STATUS (Queue)
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.

RETENTION
Specifies the retention setting for the queue. If RETENTION = ON, all messages sent or received on conversations using this queue are retained in the queue until the conversations have ended. This allows you to retain messages for auditing purposes, or to perform compensating transactions if an error occurs

Note

Setting RETENTION = ON can reduce performance. This setting should only be used if required to meet the service level agreement for the application.

ACTIVATION
Specifies information about the stored procedure that is activated to process messages that arrive in this queue.

STATUS (Activation)
Specifies whether or not the queue activates 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 activate the stored procedure.

PROCEDURE_NAME = <procedure>
Specifies the name of the stored procedure to activate when the queue contains messages to be processed. This value must be a SQL Server identifier. For more information about activation, see Understanding When Activation Occurs.

database_name (procedure)
Is the name of the database that contains the stored procedure.

schema_name (procedure)
Is the name of the schema that owns the stored procedure.

stored_procedure_name
Is the name of the stored procedure.

MAX_QUEUE_READERS = max_readers
Specifies the maximum number of instances of the activation stored procedure that the queue starts simultaneously. The value of max_readers must be a number between 0 and 32767.

EXECUTE AS
Specifies the SQL Server database user account under which the activation stored procedure runs. SQL Server must be able to check the permissions for this user at the time that the queue activates the stored procedure. For Windows domain user, the SQL Server must be connected to the domain and able to validate the permissions of the specified user when the procedure is activated or activation fails. For a SQL Server user, the server can always check permissions.

SELF
Specifies that the stored procedure executes as the current user. (The database principal executing this ALTER QUEUE statement.)

'user_name'
Is the name of the user that the stored procedure executes as. user_name must be a valid SQL Server user specified as a SQL Server identifier. The current user must have IMPERSONATE permission for the user_name specified.

OWNER
Specifies that the stored procedure executes as the owner of the queue.

DROP
Deletes all of the activation information associated with the queue.

Remarks

When a queue with a specified activation stored procedure contains messages, changing the activation status from OFF to ON immediately activates the activation stored procedure. Altering the activation status from ON to OFF stops the broker from activating instances of the stored procedure, but does not stop instances of the stored procedure that are currently running.

Altering a queue to add an activation stored procedure does not change the activation status of the queue. Changing the activation stored procedure for the queue does not affect instances of the activation stored procedure that are currently running.

Service Broker checks the maximum number of queue readers for a queue as part of the activation process. Therefore, altering a queue to increase the maximum number of queue readers allows Service Broker to immediately start more instances of the activation stored procedure. Altering a queue to decrease the maximum number of queue readers does not affect instances of the activation stored procedure currently running. However, Service Broker does not start a new instance of the stored procedure until the number of instances for the activation stored procedure falls below the configured maximum number. For a more detailed description of the activation process, see Understanding When Activation Occurs.

When a queue is unavailable, Service Broker holds messages for services that use the queue in the transmission queue for the database. The sys.transmission_queue catalog view provides a view of the transmission queue.

If a RECEIVE statement or a GET CONVERSATION GROUP statement specifies an unavailable queue, that statement fails with a Transact-SQL error.

Permissions

Permission for altering a queue defaults to the owner of the queue, members of the db_ddladmin or db_owner fixed database roles, and members of the sysadmin fixed server role.

Examples

A. Making a queue unavailable

The following example makes the ExpenseQueue queue unavailable to receive messages.

ALTER QUEUE ExpenseQueue WITH STATUS = OFF ;

B. Changing the activation stored procedure

The following example changes the stored procedure that the queue starts. The stored procedure executes as the user who ran the ALTER QUEUE statement.

ALTER QUEUE ExpenseQueue
    WITH ACTIVATION (
        PROCEDURE_NAME = new_stored_proc,
        EXECUTE AS SELF) ;

C. Changing the number of queue readers

The following example sets to 7 the maximum number of stored procedure instances that Service Broker starts for this queue.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (MAX_QUEUE_READERS = 7) ;

D. Changing the activation stored procedure and the EXECUTE AS account

The following example changes the stored procedure that Service Broker starts. The stored procedure executes as the user SecurityAccount.

ALTER QUEUE ExpenseQueue
    WITH ACTIVATION (
        PROCEDURE_NAME = AdventureWorks.dbo.new_stored_proc ,
        EXECUTE AS 'SecurityAccount') ;

E. Setting the queue to retain messages

The following example sets the queue to retain messages. The queue retains all messages sent to or from services that use this queue until the conversation that contains the message ends.

ALTER QUEUE ExpenseQueue WITH RETENTION = ON ;

F. Removing activation from a queue

The following example removes all activation information from the queue.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (DROP) ;

Share via