CREATE QUEUE

Create a message queue.

Syntax
      CREATE 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 } 
               ) ]
      ]
        [ ON { filegroup | [DEFAULT] } ] 
      [;]

Key
   queue     The queue to be created.
   database  Database within which to create the new 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.

When a message arrives for a service, the Service Broker will place the message on the queue associated with the service.

Example

CREATE QUEUE MyQueue1;

CREATE QUEUE MyQueue2
    WITH STATUS = ON,
      RETENTION = OFF,
      ACTIVATION (
          PROCEDURE_NAME = MyDatabase.dbo.myproc,
          MAX_QUEUE_READERS = 10,
          EXECUTE AS SELF )
    ON [DEFAULT] ;

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

Related commands:

ALTER QUEUE
DROP QUEUE
Equivalent Oracle command: DBMS_JOB


© Copyright SS64.com 1999-2014
Some rights reserved