CREATE QUEUE (Transact-SQL)

Creates a new queue in a database. Queues store messages. When a message arrives for a service, Service Broker puts 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 } 
            ) [ , ] ]
     [ POISON_MESSAGE_HANDLING (
       [ STATUS = { ON | OFF } )
    ]
     [ 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. database_name must specify the name of an existing database. When database_name is not 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, schema_name must be the default schema for the user who 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 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 that use this queue are retained in the queue until the conversations have ended. This lets you 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 decrease performance. This setting should only be used if it is required for the application. For more information, see Message Retention.

• ACTIVATION
  Specifies information about which stored procedure you have to start to process messages in this queue.

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

• PROCEDURE_NAME = <procedure>
  Specifies the name of the stored procedure to start 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 starts the stored procedure. For a domain user, the server must be connected to the domain when the procedure is started 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 who 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.

• POISON_MESSAGE_HANDLING
  Specifies whether poison message handling is enabled for the queue. 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.

• 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 might not be a temporary object. Therefore, queue names starting with # are not valid.

Creating a queue in an inactive state lets you 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 time.

Permissions for the activation stored procedure are checked when Service Broker starts 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.

Column name	Data type	Description
status	tinyint	Status of the message. The RECEIVE statement returns all messages that have a status of 1. If message retention is on, the status is then set to 0. If message retention is off, the message is deleted from the queue. Messages in the queue can contain one of the following values: 0=Retained received message 1=Ready to receive 2=Not yet complete 3=Retained sent message
priority	tinyint	The priority level that is assigned to this message.
queuing_order	bigint	Message order number in the queue.
conversation_group_id	uniqueidentifier	Identifier for the conversation group that this message belongs to.
conversation_handle	uniqueidentifier	Handle for the conversation that this message is part of.
message_sequence_number	bigint	Sequence number of the message in the conversation.
service_name	nvarchar(512)	Name of the service that the conversation is to.
service_id	int	SQL Server object identifier of the service that the conversation is to.
service_contract_name	nvarchar(256)	Name of the contract that the conversation follows.
service_contract_id	int	SQL Server object identifier of the contract that the conversation follows.
message_type_name	nvarchar(256)	Name of the message type that describes the message.
message_type_id	int	SQL Server object identifier of the message type that describes the message.
validation	nchar(2)	Validation used for the message. E=Empty N=None X=XML
message_body	varbinary(MAX)	Content of the message.
message_id	uniqueidentifier	Unique identifier for the message.

Permissions

Permission for creating a queue uses 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 starts the stored procedure 2008R2.dbo.expense_procedure to process messages. The stored procedure executes as the user who 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 = AdventureWorks2008R2.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)

Concepts

Queues

Building Applications with Service Broker

Service Broker Activation

Internal Activation Context

Service Broker Tutorials