Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-10-27 : 06:47:40
|
| I have a job with multiple steps. If one step succeeds or fails, i want it to go onto the next step. However, I want the job as a whole to report as failed if any steps fail(as opposed to the warning icon). How can I do this?Hearty head pats |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-10-28 : 04:21:45
|
| Hi TaraThanks for your reply. How would I do this? I assume I create an additional step that will execute the RAISERROR block, but I want to make sure that all steps execute prior to failing the job. For instance, I have 10 steps, steps 2 and 8 fail, but on failure, they go to the next step so that the rest all execute. How do I know that 2 and 8 fail? Is there a way to store the results (@@ERROR) in global parameters that I can check in the final step?Hearty head pats |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bmckd65
Starting Member
1 Post |
Posted - 2009-12-16 : 18:40:16
|
| Alternatively you could do something similar to following. If the count is greater than 0 then you have an error in one of the steps. Bear in mind that if you include the following SQL as a step in your job, and it fails once, then it will report a failure forever as it will detect a last_run_outcome of 0 for itself.[use msdb]if (select count(*) FROM [msdb].[dbo].[sysjobsteps]where [last_run_outcome] = 0 and job_id = (select job_id from dbo.sysjobswhere name = '<job_name>')) > 0 BEGIN RAISERROR('Failure detected in <job_name>.', 17, 1)END |
|
|
|
|
|
|
How to make job report 'failure'