How to run ALTER QUEUES on multiple queues on SQL Server-CodePudding

I have to write a SQL query for a SQL Server database to check if multiple sys queues in service broker are disabled, and then enable them if found disabled.

I can get the list of queues with below, but unable to figure out how to run ALTER QUEUE query on all of them:

with queues as 
(
    select name 
    from sys.service_queues
    where is_enqueue_enabled = 0
)
select * from queues

CodePudding user response：

You'll need to use dynamic SQL for this task. The example below uses STRING_AGG to build the script, which assumes SQL 2017 or later.

DECLARE @SQL nvarchar(MAX) = (
    SELECT STRING_AGG(N'ALTER QUEUE '   QUOTENAME(name)   N' WITH STATUS = ON',';')   N';'
    FROM sys.service_queues
    WHERE is_enqueue_enabled = 0
    );
EXEC sp_executesql @SQL;

CodePudding user response：

As an alternative to your approach, powershell is pretty good at this sort of thing. My general rule is "if I'm writing dynamic SQL to do DDL, powershell should at least be considered".

I'm utilizing the Get-DbaDatabase cmdlet from the dbatools module as a convenience to get a database SMO object, but that's not necessary (i.e. you could instantiate it yourself).

$db = Get-DbaDatabase -SqlInstance . -Database AdventureWorks2019;

$queues = $db.ServiceBroker.Queues |
   where IsEnqueueEnabled -eq $false;

foreach ($q in $queues) {
   $q.IsEnqueueEnabled = $true;
   $q.Alter();
}

This approach becomes especially compelling if you need to run over several databases and/or several servers.