Friday, July 14, 2023

SQL Server Agent - How to enable SQL Agent Job using T-SQL script

USE MSDB;
GO

--Declaring Variable
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @Cnt INT
DECLARE @Min INT
--Declaring Table Variable
DECLARE @SQLAgentJob (
	Id INT IDENTITY
	,Job_Id UNIQUEIDENTIFIER
	)

-- Setting minimum value to 1
SET @Min = 1

--Inserting all the Job IDs which are disabled for my application MyAppProcessing
INSERT INTO @SQLAgentJob
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 0
--Remove this filter in case need  to enable all the available jobs in SQL instance
AND Name LIKE 'MyAppProcessing%'

-- Count of total Job disabled
SELECT @Cnt = COUNT(Job_Id)
FROM @SQLAgentJob

--While Loop
WHILE @Min <= @Cnt
BEGIN
	SELECT @job_id = job_id
	FROM @SQLAgentJob
	WHERE ID = @Min

	EXEC msdb.dbo.sp_update_job @job_id = @job_id
		,@enabled = 1

	SET @Min = @Min + 1
END

No comments:

Post a Comment