How to enable and disable a queue using SMO

The SMO object model for SQL Server ServiceQueue does allow one to enable or disable a queue, but the property that modifies the queue status is not intuitive, it is IsEnqueueEnabled:

Gets or sets the Boolean property that specifies whether the queue is enabled.

This property matches the catalog view column is_enqueue_enabled in sys.service_queues but bears little resemblance to the T-SQL statement used to enable or disable a queue: ALTER QUEUE … WITH STATUS = {ON|OFF}

For example the following SMO code snippet:

<pre> Server server = new Server("..."); Database db = server.Databases["msdb"]; ServiceQueue sq = new ServiceQueue(db.ServiceBroker, "foo"); sq.Create(); sq.IsEnqueueEnabled = false; sq.Alter(); sq.IsEnqueueEnabled = true; sq.Alter(); </pre> <p>

generates the following T-SQL:

<pre> CREATE QUEUE [dbo].[foo]; ALTER QUEUE [dbo].[foo] WITH STATUS = OFF ...; ALTER QUEUE [dbo].[foo] WITH STATUS = ON ...; </pre> <p>