How do I enable a service broker queue when it is disabled?
Rédigé par Sozezzo - - Aucun commentaire
You can manually disable or enable the service through SQL Server Management Studio or run the ALTER QUEUE command for:
-- Enable ALTER QUEUE [queuename] WITH STATUS = ON; -- Disable ALTER QUEUE [queuename] WITH STATUS = OFF;
But, it can be complicated when you need over all database.
This script enable all queue over all databases:
print '-- SQL Script enable all queue on all databases' print '-- '+ @@servername print '-- '+ CAST(getdate() as nvarchar(50)); SET NOCOUNT ON; Declare @sql nvarchar(max); declare @dbname nvarchar(255); BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH; SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE' AND (f.database_id > 4) AND is_broker_enabled = 1; BEGIN TRY DROP TABLE #temp; END TRY BEGIN CATCH END CATCH; CREATE TABLE #temp( [Id] INT IDENTITY, [DatabaseName] [nvarchar](200) NOT NULL, [Schemaname] [sysname] NOT NULL, [QueueName] [sysname] NOT NULL, [Sql] NVARCHAR(MAX) ) ON [PRIMARY] DECLARE @sqlQueueOn AS NVARCHAR(MAX) = 'USE [@(dbname)]; ALTER QUEUE [@(SchemaName)].[@(QueueName)] WITH STATUS = ON;' DECLARE @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'; while ((select count(*) from #db)>0) BEGIN SET @sql = ''; SELECT TOP 1 @dbname = [name] FROM #db f ORDER BY f.name; DELETE FROM #db WHERE [name] = @dbname; SET @sql = REPLACE(@sqlSearch, '@(dbname)',@dbname); --PRINT @sql; EXEC (@sql); END UPDATE #temp SET [Sql] = @sqlQueueOn; UPDATE #temp SET [Sql] = REPLACE([Sql], '@(dbname)',[DatabaseName]); UPDATE #temp SET [Sql] = REPLACE([Sql], '@(SchemaName)',[Schemaname]); UPDATE #temp SET [Sql] = REPLACE([Sql], '@(QueueName)',[QueueName]); DECLARE @id INT; WHILE ((SELECT COUNT(*) FROM #temp) > 0) BEGIN SELECT TOP 1 @id = [Id], @sql = [Sql] FROM #temp; DELETE FROM #temp WHERE [Id] = @id; PRINT @sql; EXEC(@sql); END