Brandie Tarvin (1/6/2012)

---

Does anyone know why a Transact-SQL job step that executes a stored procedure and returns a "Query timeout" response continues on as if the job step succeeded?

Is there anyway to prevent this behavior?

azdzn (1/6/2012)

---

The step must have "Go to the next step" as "On failure Action".
See advanced tab in Job STep properties to set up this option.

MysteryJimbo (1/6/2012)

---

I may be missing the obvious. How do you generate a "Query Timeout" in a stored procedure in a SQL Agent Job?

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.

Evil Kraig F (1/6/2012)

---

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.

jnuqui (1/8/2012)

---

FYI that SQL server checks your script before you could create the job. :D it should also fail during job creation.

jnuqui (1/8/2012)

---

you may want to share your script for this. so we could discuss this weird event. :)

Brandie Tarvin (1/9/2012)

---

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).

Evil Kraig F (1/10/2012)

---

Hm, what about something like this:

TRY
(Exec MyServer.MyDB.MySchema.MyProc)
CATCH
(SELECT IDontExist FROM TableNotThere)
END