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 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.

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.

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.

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.

A. Creating a queue with no parameters

CREATE QUEUE ExpenseQueue ;

B. Creating an unavailable queue

CREATE QUEUE ExpenseQueue WITH STATUS=OFF ;

C. Creating a queue and specify internal activation information

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

CREATE QUEUE ExpenseQueue
    ON ExpenseWorkFileGroup ;

E. Creating a queue with multiple parameters

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

Reference

Other Resources

Help and Information