How to Report Failure in a Multi Steps job

[Andel]

I have a scheduled job which backup all databases on my server. I have 10 databases to backup, so I created 10 job steps -- one job step per database.

If the first step fails, I want SQL server to continue on with the rest of the steps (step 2 - 9) without quiting. So, even though steps 2 - 9 succeed, I want SQL server to report failure because the first step failed. Is this possible?




Andel
andel@barroga.net

http://www.iluko.com

 

[hmckillop]

If you are using enterprise manager to set this up, to ensure the next step is taken regardless of the previous step's result, use the advanced tab when editing the job step and then select the option to "on failure move to next step".
To report an error at the previous step you can use raiserror or log to the event log as part of each individual step - i.e. in the job step sql code.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Andel]

Thanks hmckillop for your response. Basically I want the error to be seen in SQL Agent job itself. So, here's a code that I put as the last step of the job. It works fine but I want to know if there's any better way.


declare @cmdstr varchar(1000)
declare @jobname varchar(100)
declare @rundate varchar(10)

select @jobname = 'Backup - Large Databases'
select @rundate = (select max(run_date) from msdb..sysjobhistory
where job_id = (select job_id from msdb..sysjobs where name = @jobname))

if exists(
select * from msdb..sysjobhistory
where job_id = (select job_id from msdb..sysjobs where name = @jobname)
and step_id <> 0 -- step_id 0 is the outcome of the job
and run_status <> 1 -- run_status 1 means succeeded
and run_date = @rundate -- this is the last run date
)
begin
select x from x -- LET IT FAIL TO REPORT FAILURE
end

else
select 'x' = 'x' -- REPORT SUCCESS


Andel
andel@barroga.net

http://www.iluko.com