How Do I Enable a Service Broker Queue When It Is Disabled?

1
2-- Enable
3ALTER QUEUE [queuename] WITH STATUS = ON;
4
5-- Disable
6ALTER QUEUE [queuename] WITH STATUS = OFF;

 1
 2print '-- SQL Script enable all queue on all databases'
 3print '-- '+ @@servername
 4print '-- '+ CAST(getdate() as nvarchar(50));
 5SET NOCOUNT ON;
 6Declare @sql nvarchar(max);
 7declare @dbname nvarchar(255);
 8BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH;
 9SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE' AND (f.database_id > 4) AND is_broker_enabled = 1;
10
11BEGIN TRY DROP TABLE #temp; END TRY BEGIN CATCH END CATCH;
12CREATE TABLE #temp(
13    [Id] INT IDENTITY,
14    [DatabaseName] [nvarchar](200) NOT NULL,
15    [Schemaname] [sysname] NOT NULL,
16    [QueueName] [sysname] NOT NULL,
17    [Sql] NVARCHAR(MAX)
18) ON [PRIMARY]
19
20DECLARE @sqlQueueOn AS NVARCHAR(MAX) = 'USE [@(dbname)]; ALTER QUEUE [@(SchemaName)].[@(QueueName)] WITH STATUS = ON;'
21DECLARE @sqlSearch AS NVARCHAR(MAX) = 'insert into #temp (DatabaseName, SchemaName, QueueName) select ''@(dbname)'' as DatabaseName, s.name as Schemaname, q.name as QueueName FROM @(dbname).sys.service_queues q INNER JOIN @(dbname).sys.schemas s ON q.schema_id = s.schema_id where is_ms_shipped = 0 AND is_enqueue_enabled = 0';
22
23while ((select count(*) from #db)>0)
24BEGIN
25
26    SET @sql = '';
27    SELECT TOP 1 @dbname = [name] FROM #db f ORDER BY f.name;
28    DELETE FROM #db WHERE [name] = @dbname;
29
30    SET @sql = REPLACE(@sqlSearch, '@(dbname)',@dbname);
31    --PRINT @sql;
32    EXEC (@sql);
33
34END
35
36UPDATE #temp SET [Sql] = @sqlQueueOn;
37UPDATE #temp SET [Sql] = REPLACE([Sql], '@(dbname)',[DatabaseName]);
38UPDATE #temp SET [Sql] = REPLACE([Sql], '@(SchemaName)',[Schemaname]);
39UPDATE #temp SET [Sql] = REPLACE([Sql], '@(QueueName)',[QueueName]);
40
41DECLARE @id INT;
42WHILE ((SELECT COUNT(*) FROM #temp) > 0)
43BEGIN
44    SELECT TOP 1 @id = [Id], @sql = [Sql] FROM #temp;
45    DELETE FROM #temp WHERE [Id] = @id;
46    PRINT @sql;
47    EXEC(@sql);
48END