CREATE QUEUE (Transact-SQL)

Creates a new queue in a database. Queues store messages. When a message arrives for a service, Service Broker places the message on the queue associated with the service.

Transact-SQL Syntax Conventions

Syntax

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

<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 within which to create the new queue. The database_name must specify the name of an existing database. When no database_name is provided, the queue is created in the current database.

• schema_name (object)
  Is the name of the schema to which the new queue belongs. The schema defaults to the default schema for the user that executes the statement. If the CREATE QUEUE statement is executed by a member of the sysadmin fixed server role, or a member of the db_dbowner or db_ddladmin fixed database roles in the database specified by database_name, schema_name can specify a schema other than the one associated with the login of the current connection. Otherwise, the schema_name specified must be the default schema for the user that executes the statement.

• queue_name
  Is the name of the queue to create. This name must meet the guidelines for SQL Server identifiers.

• 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. You can create the queue in an unavailable state in order 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 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. If this clause is not specified, the retention setting defaults to OFF.

  Note

  Setting RETENTION = ON can reduce performance. This setting should only be used if required for the application. For more information, see Message Retention.

• ACTIVATION
  Specifies information about the stored procedure to activate to process messages in this queue.

• STATUS (Activation)
  Specifies whether or not Service Broker 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. If this clause is not specified, the default is ON.

• PROCEDURE_NAME = <procedure>
  Specifies the name of the stored procedure to activate to process messages in this queue. This value must be a SQL Server identifier. For more information, 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 contains the stored procedure.

• 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 at the same time. 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 a domain user, the server must be connected to the domain 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 CREATE QUEUE statement.)

• 'user_name'
  Is the name of the user that the stored procedure executes as. The user_name parameter 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.

• ON filegroup | [ DEFAULT ]
  Specifies the SQL Server filegroup on which to create this queue. You can use the filegroup parameter to identify a filegroup, or use the DEFAULT identifier to use the default filegroup for the service broker database. In the context of this clause, DEFAULT is not a keyword, and must be delimited as an identifier. When no filegroup is specified, the queue uses the default filegroup for the database.

Remarks

A queue can be the target of a SELECT statement. However, the contents of a queue can only be modified using statements that operate on Service Broker conversations, such as SEND, RECEIVE, and END CONVERSATION. A queue cannot be the target of an INSERT, UPDATE, DELETE, or TRUNCATE statement.

A queue may not be a temporary object. Therefore, queue names beginning with # are not valid.

Creating a queue in an inactive state allows you to get the infrastructure in place for a service before allowing messages to be received on the queue.

Service Broker does not stop activation stored procedures when there are no messages on the queue. An activation stored procedure should exit when no messages are available on the queue for a short period of time.

Permissions for the activation stored procedure are checked when Service Broker activates the stored procedure, not when the queue is created. The CREATE QUEUE statement does not verify that the user specified in the EXECUTE AS clause has permission to execute the stored procedure specified in the PROCEDURE NAME clause.

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.

A queue is a schema-owned object. Queues appear in the sys.objects catalog view.

The following table lists the columns in a queue.

Permissions

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

REFERENCES permission for 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.

RECEIVE permission for a queue defaults to the owner of the queue, members of the db_owner fixed database role, and members of the sysadmin fixed server role.

Examples

A. Creating a queue with no parameters

The following example creates a queue that is available to receive messages. No activation stored procedure is specified for the queue.

CREATE QUEUE ExpenseQueue ;

B. Creating an unavailable queue

The following example creates a queue that is unavailable to receive messages. No activation stored procedure is specified for the queue.

CREATE QUEUE ExpenseQueue WITH STATUS=OFF ;

C. Creating a queue and specify internal activation information

The following example creates a queue that is available to receive messages. The queue starts the stored procedure expense_procedure when a message enters the queue. The stored procedure executes as the user ExpenseUser. The queue starts a maximum of 5 instances of the stored procedure.

CREATE QUEUE ExpenseQueue
    WITH STATUS=ON,
    ACTIVATION (
        PROCEDURE_NAME = expense_procedure,
        MAX_QUEUE_READERS = 5,
        EXECUTE AS 'ExpenseUser' ) ;

D. Creating a queue on a specific filegroup

The following example creates a queue on the filegroup ExpenseWorkFileGroup.

CREATE QUEUE ExpenseQueue
    ON ExpenseWorkFileGroup ;

E. Creating a queue with multiple parameters

The following example creates a queue on the DEFAULT filegroup. The queue is unavailable. Messages are retained in the queue until the conversation that they belong to ends. When the queue is made available through ALTER QUEUE, the queue activates the stored procedure AdventureWorks.dbo.expense_procedure to process messages. The stored procedure executes as the user that ran the CREATE QUEUE statement. The queue starts a maximum of 10 instances of the stored procedure.

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

See Also

Reference

ALTER QUEUE (Transact-SQL)
CREATE SERVICE (Transact-SQL)
DROP QUEUE (Transact-SQL)
RECEIVE (Transact-SQL)
EVENTDATA (Transact-SQL)

Other Resources

Queues
Service Broker Tutorials
Building Applications With Service Broker
Service Broker Activation
Internal Activation Context

Help and Information

Getting SQL Server 2005 Assistance