How can I check SQL AGENT Job status in an precedence expression
I am running multiple tasks in a SQL Server Agent Job, and need to verify the job has ran and completed successfully before allowing the next task to begin. Is there a way to do this using an expression in the precedence constraint?
Could you leverage built in features of SQL Agent like creating an additional step so the first step has to complete before the second one? Another option if you need only one step in your Job is based on condition you could tell the step to execute another SQL Agent Job. If this is helpful please mark the response as answered.
Created a query to return a value to a scalar. Use the scalar in a loop with a delay until the status is "Suceeded", and then allow the next job to start. Declare @MyStatus nvarchar(10) Set @MyStatus = (Select CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' -- In Progress END AS [LastRunStatus] FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN ( SELECT [job_id] , [run_date] , [run_time] , [run_status] , [run_duration] , [message] , ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1 Where sJOB.name Like 'Index Maintenance Job%') Thanks for your time and knowledge!