CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016sql-server-agent
Philip
Philip

Reputation: 2628

Disable all SQL Agent Jobs in a particular category

I have the following query that generates the commands necessary for me to disable all the SQL Agent jobs particular to a category. 

Select 'Exec MSDB.dbo.sp_update_job @job_name = N''' + SJ.Name + ''', @Enabled = 0'
  FROM msdb..sysjobs SJ inner join msdb..syscategories SC
    on SJ.category_id = SC.category_id
 Where SJ.[Enabled] = 1
   and SC.Name = 'BI ETLs'

How can I automate this totally, so it will generate the commands and execute them without it being a manual process?

Upvotes: 1

Views: 2104

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

You could use dynamic SQL:

DECLARE @sql NVARCHAR(MAX);

SET @sql = STUFF((    
  SELECT ';' + 'Exec MSDB.dbo.sp_update_job @job_name = N''' 
           + SJ.Name + ''', @Enabled = 0'
  FROM msdb..sysjobs SJ JOIN msdb..syscategories SC
    ON SJ.category_id = SC.category_id
 WHERE SJ.[Enabled] = 1
   and SC.Name = 'BI ETLs' FOR XML PATH('')),1 ,1, '') ;

EXEC dbo.sp_executesql @sql;

With SQL Server 2017 it is much easier to concatenate strings using STRING_AGG:

DECLARE @sql NVARCHAR(MAX);

SET @sql = (Select STRING_AGG('Exec MSDB.dbo.sp_update_job @job_name = N''' + SJ.Name + ''', @Enabled = 0', ';')
  FROM msdb..sysjobs SJ inner join msdb..syscategories SC
    on SJ.category_id = SC.category_id
 Where SJ.[Enabled] = 1
   and SC.Name = 'BI ETLs');

EXEC dbo.sp_executesql @sql;

Upvotes: 3

Related Questions