September 1, 2010 at 9:57 am
I have a job with about 15 steps. One step occasionally times out. I get this error message:
OLE DB provider "SQLNCLI" for linked server "SFAPROD" returned message "Query timeout expired". [SQLSTATE 01000] (Message 7412). The step succeeded.
The step is set to "On success action - Go to next step." and "On failure action - Go to a different step"
The problem is that when the query times out with this error, the step reports success and goes to the next step instead of being rerouted to the alternate step. The step's database command is an EXEC stored procedure. Is there a way to get this step to report a failure and behave accordingly?
Thanks,
September 1, 2010 at 10:11 am
Does the stored procedure have a TRY...CATCH block within it?
I would expect that to throw an error in normal circumstances...
September 1, 2010 at 10:51 am
Knowing what is inside that stored procedure as well as how you have the linked server configured could help figure out the root of this issue.
Could you explain your linked server setup as well as give us more info on the proc?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 1, 2010 at 1:04 pm
Thanks for the answers.
The SP does not have a Try Catch block, good idea. Would I do something like a Return 1 for a failure?
I am not sure what to say about the Linked Server, I don't know a lot about them. It is a separate physical box (SQL 2005) on the same subnet. It has a very high time out configuration. What other sorts of things would be useful to know?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply