ALTER QUEUE (Transact-SQL)
Changes the properties of a queue.
.gif "Ikona łącza do tematu")
Transact-SQL Syntax Conventions
Składnia
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 }
) [ , ]]
[ POISON_MESSAGE_HANDLING (
STATUS = { ON | OFF } )
]
[ ; ]
<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[!UWAGA]
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.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_reader
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.POISON_MESSAGE_HANDLING
Specifies whether poison message handling is enabled. The default is ON.A queue that has poison message handling set to OFF will not be disabled after five consecutive transaction rollbacks. This allows for a custom poison message handing system to be defined by the application.
Uwagi
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.
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 = AdventureWorks2012.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) ;