Brandie Tarvin (1/9/2012)
Evil Kraig F (1/6/2012)
Brandie Tarvin (1/6/2012)
To be clear: I'm not concerned with fixing the timeout issue itself. I've got that covered. What I'm concerned with is how to force a SQL Agent job step to fail if it runs into a timeout issue.I believe you'll have to wrap the call with a TRY/CATCH and do a custom RAISEERROR if it occurs, but I don't have the ability to modify my network to create a mini-lab to confirm I can see the same functionality and that it'll cure it.
Craig, I get what you're saying, but I can't figure out how to pass the error outside of the code context to fail the job step itself. Raising an error is, in and of itself, a successful prosecution of the code. And I see no options within the job step window that allow me to set success or failure of that job step.
The only option I'm seeing is having that error attempt to update the sysjobsteps table, but I hate the idea of updating sys tables (if I could even do that anymore).
Hm, what about something like this:
TRY
(Exec MyServer.MyDB.MySchema.MyProc)
CATCH
(SELECT IDontExist FROM TableNotThere)
END
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA