March 13, 2009 at 2:37 pm
[p]I have a maintenance plan task that calls a SQL Agent job. The job fails, but the maintenance plan succeeds. How can I get the job failure to flow back into the maintenance plan task that calls it? (I am using SS 2005 SP 2, no CUs.)
[/p][p]
Steps to Replicate:
[/p][p]
Create a SQL Agent job that fails (e.g. set the only step to "quit the job reporting failure" upon both success and failure), then Create a maintenance plan with a task that calls the job. Execute the maintenance plan. It succeeds (!?).
[/p][p]
Create two additional tasks that send different messages or write different text to a log file. Link them appropriately to the task that calls the SQL Agent job, one linked for success and one linked for failure (OR condition). Execute the maintenance plan. The failure task does not execute. (!?)
[/p]
March 13, 2009 at 3:17 pm
You can't - the only time you will get an error in the maintenance plan is if the job cannot be started.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2009 at 4:09 pm
[p]Thanks for the quick response. That's Microsoft for you!
[/p][p]
My requirement is to call a VBScript (via the CScript host) to do some things as part of a maintenance plan, and I need to be able to react to the script's return code. Due to limitations in VBScript, success will result in a non-zero CScript errorlevel being set upon termination.
[/p][p]
I do not want to enable xp_cmdshell. Aside from SQLCLR, is there a way to call a VBScript from a maintenance plan task? (The VBscript needs to use WSHSell.Exec and FileSystemObject).
in[/p]
March 13, 2009 at 4:17 pm
The only real option you have is to use a SQL Agent job to run your script, and in the next step call your maintenance plan.
The other options are xp_cmdshell or SQLCLR.
One thing you might want to consider is moving away from VBScript and look at Powershell. It wouldn't solve this problem, but it is the direction MS is moving and they have even added the provider into SQL Agent.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2009 at 5:18 pm
I hadn't thought of calling the maintenance plan from the hand-written SQL Agent job that calls the script. That just might fit the bill. Thanks!
Unfortunately, powershell is not an option in our shop yet.
I can see SQLCLR in my near future ...
March 13, 2009 at 5:28 pm
larry Hennig (3/13/2009)
I hadn't thought of calling the maintenance plan from the hand-written SQL Agent job that calls the script. That just might fit the bill. Thanks!Unfortunately, powershell is not an option in our shop yet.
I can see SQLCLR in my near future ...
Yeah - I don't understand the reluctance about powershell myself - but I see it all the time.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply