April 26, 2004 at 12:41 pm
Is it possible (and how is it done) to run a program if a Maintenance Plan fails? or a job fails?
Thanks for the help,
Steve
April 26, 2004 at 1:25 pm
You could use alerts to run a job on failure, however I have not been able to figure that out myself.
Another option is to put a trigger on the msdb..sysjobs table that looks for an inserted value of 0 indicating a failure, and then use that to execute a job. This would also have the advantage of capturing failures of the maintenance plan.
April 27, 2004 at 5:41 am
Add a new step to the maintenance job. Have that step exec sp_start_job:
sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id
[,[@error_flag =] error_flag]
[,[@server_name =] 'server_name']
[,[@step_name =] 'step_name']
[,[@output_flag =] output_flag]
ex: exec sp_start_job 'Run when Maintenance plan fails' , 'ThisServer'
Go to the advanced tab of the properties of the first step, and set it to go to the next step (the new one you just made) on FAILURE.
IF the job is on another server entirely set the second step to (here's my way)
1) exec xp_cmdshell d:\runthisbat.bat -
2) have that bat be a osql command that exec's the sp_start_job on the other server
Keep it simple.
Thanks, and don't forget to Chuckle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply