How do you use the CREATE QUEUE statement to create a new queue for Service Broker?
RoseHrs
Posted by RoseHrs
Last Updated: July 16, 2024
SQL ServiceBroker
To create a new queue for Service Broker in SQL Server, you use the CREATE QUEUE statement. This statement allows you to define a queue where messages can be stored and processed asynchronously. Here's the basic syntax for the CREATE QUEUE statement: 
CREATE QUEUE [schema_name].[queue_name]
[
    WITH 
    (
        [ RETENTION = { ON | OFF } ]
        [, 
        [ MAX_QUEUE_READERS = max_readers ]
        [, 
        [ ACTIVATION = ( STATUS = { ON | OFF } 
            [ , PROCEDURE_NAME = schema_name.procedure_name ]
            [ , MAX_QUEUE_READERS = max_readers ]
            [ , EXECUTE AS = { SELF | OWNER | 'user_name' } ] 
            ]
        ) ]
        [, 
        [ PERSISTED = { ON | OFF } ] 
        ]
    )
]
Parameters:
- schema_name: The schema under which the queue is created. - queue_name: The name of the queue you want to create. - RETENTION: Specifies if the messages in the queue should be retained after being processed. - MAX_QUEUE_READERS: Sets the maximum number of concurrent readers that can process messages from the queue. - ACTIVATION: Enables or disables queue activation, and it allows you to specify the stored procedure that will be executed automatically to process messages. - EXECUTE AS: Specifies the security context under which the stored procedure runs if ACTIVATION is enabled.
Example:
Here's an example of creating a new queue named MyQueue in the dbo schema: 
CREATE QUEUE dbo.MyQueue
WITH
(
    RETENTION = OFF,
    MAX_QUEUE_READERS = 5,
    ACTIVATION = 
    (
        STATUS = ON,
        PROCEDURE_NAME = dbo.MyQueueReader,
        MAX_QUEUE_READERS = 2,
        EXECUTE AS SELF
    )
);
Explanation of the Example:
- dbo.MyQueue is the name of the queue. - RETENTION = OFF means that messages are removed from the queue after they are read. - MAX_QUEUE_READERS = 5 allows up to 5 concurrent readers for the queue. - The ACTIVATION set to ON specifies that the stored procedure dbo.MyQueueReader will automatically be called to process messages when they arrive in the queue. - MAX_QUEUE_READERS = 2 inside the activation means that up to 2 instances of the MyQueueReader procedure can run concurrently. - EXECUTE AS SELF means the procedure will run in the context of the user who created the queue.
Conclusion:
By using the CREATE QUEUE statement properly, you can manage how your application processes messages with SQL Server Service Broker effectively.
Default Image