ALTER QUEUE

Change the properties of a message queue.

Syntax
      ALTER QUEUE [database.[schema]. | schema.] queue
      [ WITH
        [ STATUS = {ON | OFF}  [ , ] ]
        [ RETENTION = {ON | OFF} [ , ] ] 
        [ ACTIVATION (
           { [ STATUS = {ON | OFF} , ] 
              PROCEDURE_NAME = [database.[schema]. | schema.] stored_procedure ,
              MAX_QUEUE_READERS = max_readers , 
              EXECUTE AS { SELF | 'user_name' | OWNER } 
           | DROP }
               ) ]
      ]
        [ ON { filegroup | [DEFAULT] } ] 
      [;]

Key
   queue     The queue to be altered.
   database  Database containing the queue.
   STATUS    The queue is available (ON) or unavailable (OFF).
   RETENTION Retain all queue Messages using the conversations ends.
             (This can reduce performance)
   STATUS    When STATUS = OFF, the queue does not activate the stored procedure.
   MAX_QUEUE_READERS  Instances of the stored procedure that may run at the same time (0-32767)
   SELF      Run the stored procedure as the user who executes the CREATE QUEUE statement.
   OWNER     Run the stored procedure as the owner of the queue.
   DROP      Delete all activation info associated with the queue.

When a queue is unavailable, the Service Broker will hold its messages in the transmission queue for the database.

Examples

ALTER QUEUE MyQueue
    WITH ACTIVATION (
        PROCEDURE_NAME = MyDatabase.dbo.my_new_proc ,
        EXECUTE AS 'MyDbAccount') ;

"An Englishman, even if he is alone, forms an orderly queue of one" - George Mikes

Related commands

CREATE QUEUE
DROP QUEUE
sys.transmission_queue
Equivalent Oracle command: DBMS_JOB