How do I enable a service broker queue when it is disabled?

Rédigé par M 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

Fil RSS des articles de ce mot clé