How to enable and disable a queue using SMO
February 6th, 2013The 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:
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();
generates the following T-SQL:
CREATE QUEUE [dbo].[foo];
ALTER QUEUE [dbo].[foo] WITH STATUS = OFF ...;
ALTER QUEUE [dbo].[foo] WITH STATUS = ON ...;