When you have several sql jobs that need to be disabled all. If you can, you can just turn off SQL Agent but, sometime, it is not possible.
This script generates the script to enable and disable.
1
2--generate disable
3SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs
4
5--generate enable
6SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs
When you have your jobs organized by category, you can use this script:
1
2-------------------------------------
3-- Script to enable or disable Jobs
4-- Filter by Category
5-------------------------------------
6DECLARE @Categoryname AS NVARCHAR(100) = null;
7DECLARE @newJobStatus AS NVARCHAR(1) = '0';
8
9SET NOCOUNT ON
10
11DECLARE @JobName AS NVARCHAR(255);
12DECLARE @sqlEnable AS NVARCHAR(MAX);
13DECLARE @sql AS NVARCHAR(MAX);
14DECLARE @isEnabled AS NVARCHAR(1);
15
16SET @sql = 'EXEC msdb.dbo.sp_update_job @job_name = N''${JobName}'', @enabled = ' + @newJobStatus + ';'
17BEGIN TRY drop table #jobs; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
18SELECT replace(@sql, '${JobName}', sysjobs.NAME) AS sqlEnable
19, sysjobs.NAME AS JobName
20, job_id
21, [enabled] isEnabled
22INTO #jobs
23-- SELECT sysjobs.name 'Job Name' , job_id, [enabled] isEnabled, syscategories.name
24FROM msdb.dbo.sysjobs
25INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
26WHERE
27 (syscategories.NAME = @Categoryname OR @Categoryname IS NULL) AND NOT ([enabled] = @newJobStatus)
28
29WHILE ( EXISTS ( SELECT JobName FROM #jobs ) )
30BEGIN
31
32 SELECT TOP 1 @JobName = JobName
33 , @sqlEnable = sqlEnable
34 , @isEnabled = isEnabled
35 FROM #jobs;
36
37 DELETE FROM #jobs
38 WHERE
39 JobName = @JobName;
40
41 print @sqlEnable;
42 EXEC (@sqlEnable)
43
44END
Source : https://www.sqlservercentral.com/Forums/Topic1214637-391-1.aspx
Comments