asked
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?
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
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!
17 People are following this question.
