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
Friday, July 14, 2023
SQL Server Agent - How to enable SQL Agent Job using T-SQL script
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment