I want to set job status to Fail if one step fails

Transact-SQL
1

Hi All

I want to set the job status to "Fail" for all the steps if one of the steps failed. For Example: Refer below

Job Job_Step Step_Status Job_Status
Sales 1 Succeeded Failed
Sales 2 Failed Failed

2

Is it not enough to set the "On failure action" for the Step to "Quit the job reporting failure"?

Ipso facto none of the remaining steps have been run

3

So do you want a query created to list jobs with any failed step(s)? Or do you have existing code that''s not working?

4

Hi, I have query that lists all failed and successfully steps in a job but I want new column for "Job Status" and if one step fail then the overall job should be failed for all the steps in that job. So basically, if we have Job1 and steps s1,s2,s3 and s1 & s2 run sccessful and s3 failes then the step status should show successfull for s1 and s2 but the job status should be failed.

5

Would need the existing code to change it. What you are doing is not trivial -- given that MS didn't properly model the sysjobhistory table -- so tying the steps together is tricky, and there are several ways you could (attempt to) do it.

6

USE [msdb]
GO

/****** Object: View [dbo].[ViewJobs2] Script Date: 11/09/2015 08:07:48 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ViewJobs2]

AS

SELECT name AS Job_Name,step_id,step_name,message,date_modified Last_Run, CASE WHEN CHARINDEX('Failed', message) = 0 then 'The Step Succeeded' ELSE 'The Step Failed' END Step_Status,
CASE WHEN CHARINDEX('Failed', message) = 0 then 'The Job Succeeded'
ELSE 'The Job Failed' END Job_Status

FROM sysjobs
INNER JOIN sysjobhistory
ON sysjobs.job_id = sysjobhistory.job_id

GO

7

Not sure it is as simple as that, sadly ...

Modified Date is not changed when the job runs

If a Step Fails the job (i.e. the Step within the Job) may be set to continue with the next [or a different] step, so not sure that "Job Failed" is a correct description when a step fails - in particular the report will display "The Step Failed The Job Failed" and then for the next Step within the job "The Step Succeeded The Job Succeeded"

8

This might do? Set to only show the most recent execution. STEPs that are not executed will show as NULL (so could use IsNull() to display a message instead)

This presupposes that the current STEPS defined for the Job are the same as the steps that existed when the job actually ran!!

SELECT	J.job_id
	, J.enabled
	, [JobName] = CASE WHEN S.last_run_outcome = 1 THEN '' ELSE '*** ' END
		+ COALESCE(J.name, '(No name)')
	, S.step_id
	, S.last_run_outcome
	, S.last_run_duration
	, S.last_run_date
	, S.last_run_time
	, [StepName] = CASE WHEN S.last_run_outcome = 1 THEN '' ELSE '*** ' END
		+ COALESCE(S.step_name, '(No name)')
	, H.step_id
	, H.sql_message_id
	, H.sql_severity
	, H.run_status
	, H.run_date
	, H.run_time
	, H.run_duration
-- 	, H.operator_id_emailed
-- 	, H.server
	, H.message
	, H.step_name
--	, S.database_name
--	, J.description
FROM	msdb.dbo.sysjobs AS J
	JOIN
	(
--		Lowest Step ID for the Job
		SELECT	[S_RowNo] = ROW_NUMBER() OVER (PARTITION BY S0.job_id ORDER BY S0.step_id)
			, S0.job_id
			, S0.step_id
			, S0.last_run_outcome
			, S0.last_run_duration
			, S0.last_run_retries
			, S0.last_run_date
			, S0.last_run_time
		FROM	msdb.dbo.sysjobsteps AS S0
	) AS S0
		 ON S0.job_id = J.job_id
	JOIN msdb.dbo.sysjobsteps AS S
		 ON S.job_id = J.job_id
	LEFT OUTER JOIN msdb.dbo.sysjobhistory AS H
		 ON H.job_id = J.job_id
		AND H.step_id = S.step_id
		AND (H.run_date > S0.last_run_date OR (H.run_date = S0.last_run_date AND H.run_time >= S0.last_run_time))
WHERE	S_RowNo = 1
ORDER BY J.enabled DESC
	, S0.last_run_date DESC
	, S0.last_run_time DESC
	, J.name
	, S.step_id

I tried a variation on this which uses "History Zero" instead of "Step Zero" to determine the most recent execution time, and that could be adapted to EVERY Execution Time [of Step Zero] to provide a report of all executions [within a date/time range].

Doing this has reminded me of how shoddy MS's code is for things like this. Run Date and Time as 6-digit integers - really? Shame on MS ... it has all the appearance of being thrown together, along with Maintenance Plans, by a Summer Intern AND not checked / approved by someone more senior.