September 1, 2009 at 3:24 am
Hi All,
I have a job where the first step is to run an SP. I have error handling inside the sp so no matter what happens the SP as such runs fine, it just returns a bit value as to whether or not the steps inside were a success (1) or a failure (0).
Because the SP always completes the sql job sees it as a success and moves onto the next step (an excel SSIS job that emails out a file with the values from the first step), however i dont want this to happen if the SP returned a failure.
Is there any way of configuring the second step to only run when the SP output is 1? or am i best executing the SSIS package from within the SP at the end when all the other steps are successful?
John
September 1, 2009 at 5:51 am
You can put a RAISERROR statement within the CATCH statement of your error handler so that, assuming the error needs to be returned, you can raise it and it will respond like a procedure with an error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 1, 2009 at 6:02 am
Thanks Grant. assuming i do raise an error, i assume this still cannot be read by the sql job as to whether ot not to run the second step. I guess i could raise an error and on the raise error run a job to execute sp_stop_job so that the second step doesnt get executed.
Thanks for the suggestion
September 1, 2009 at 6:06 am
Yeah, if the procedure kicks out an error, the job should have an On Error statement that determines what to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply