To do unattended tasks like if error comes need to disable the job. In this case, we need to disable the job through T-SQL script ony. We can use this script in Job's step, if any job step failed then this script wil disable the job. This is very useful in keeping database safe from unnecessary actions from SQL Agent Job etc.
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 enabled for my application MyAppProcessing
INSERT INTO @SQLAgentJob
SELECT job_id
FROM msdb.dbo.sysjobs
WHERE enabled = 1
AND Name LIKE 'MyAppProcessing%'
-- Count of total Job enabled
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 = 0
SET @Min = @Min + 1
END
No comments:
Post a Comment