March 9, 2012 at 5:29 am
I'm running two stored procedures that basically copy data from one database into another. When I run them using Management Studio they finish with errors but the data gets copied over. If I take the same two SP's and create an agent job I'm pretty sure they stop at the error. I'm testing it with only one job step. Does this make sense to anyone? Any ideas to get the job to continue?
March 9, 2012 at 6:32 am
jackimo (3/9/2012)
I'm running two stored procedures that basically copy data from one database into another. When I run them using Management Studio they finish with errors but the data gets copied over. If I take the same two SP's and create an agent job I'm pretty sure they stop at the error. I'm testing it with only one job step. Does this make sense to anyone? Any ideas to get the job to continue?
Do you run them in one step in SSMS? If you run them in two steps in SSMS but in one step with agent, I could probably guess whats happening 😉
I think the lack of replies here probably have to do with how little info you posted, like table definitions, code, error messages, etc
March 9, 2012 at 6:49 am
Sorry for the lack of info. The issue being that there are two very long stored procedures involving hundreds of tables. I think my issue is more of a general question that doesn't require detailed information. I think when I execute my 2 procs via a T-SQL agent job the job simply stops when either of the SP's encounter an error. This doesn't happen when I execute the procs in Management Studio - I get a warning that they completed with errors.
From the bit of research I've done I think if I put the logic in a script and use a CMDEXEC job step the job will run as I want it - continue after the error. Any other advice is appreciated.....
March 9, 2012 at 6:58 am
jackimo (3/9/2012)
Sorry for the lack of info. The issue being that there are two very long stored procedures involving hundreds of tables. I think my issue is more of a general question that doesn't require detailed information. I think when I execute my 2 procs via a T-SQL agent job the job simply stops when either of the SP's encounter an error. This doesn't happen when I execute the procs in Management Studio - I get a warning that they completed with errors.From the bit of research I've done I think if I put the logic in a script and use a CMDEXEC job step the job will run as I want it - continue after the error. Any other advice is appreciated.....
Well unfortunately in my opinion, there are some complicating factors when discussing this. Firstly, SQL server has a wide variety of situations that can cause an error, some errors stop statements, some errors stop batches, some errors drop connections, etc, and secondly, your own situation is going to also dictate which errors are meaningful. So while you could encapsulate the entire routine in a manner that allows continuing on error, I would also recommend an in depth study on what errors are occuring where.
If you for instance, decide to continue on any and every error because today's error is trivial, you could be setting up for a trap in the future by continuing on a much more serious error that you might have second thoughts on if you had thought about it!
Just thinking out loud, error handling is an interesting subject for me, even more so given how much 2000 level code I have to write 😉
March 9, 2012 at 7:05 am
Here's a bit more information,
The two sp's are vendor supplied. They have given me the 2 procs and when they run in Management Studio they run exactly as expected. When I run then in a T-SQL job step they 'seem' to stop after the first error. All I want to do is schedule them and have the job behave exactly as if I ran them in Management Studio.
March 9, 2012 at 7:16 am
jackimo (3/9/2012)
Here's a bit more information,The two sp's are vendor supplied. They have given me the 2 procs and when they run in Management Studio they run exactly as expected. When I run then in a T-SQL job step they 'seem' to stop after the first error. All I want to do is schedule them and have the job behave exactly as if I ran them in Management Studio.
I see. I tend to take those sorts of issues back to the vendor, since they'd have the best insight into the specifics.
March 9, 2012 at 9:18 am
Maybe this will help someone else....
I found this quote on Ola Hallengren's site (http://ola.hallengren.com/frequently-asked-questions.html) discussing running T-SQL job steps;
"The stored procedures must be executed in a CmdExec job step with sqlcmd and the -b option, if error handling and logging are to work as designed. The problem with the T-SQL job step is that it stops executing after the first error."
This was exactly the behavior I was seeing. So all I had to do was paste the logic into a script and execute it with sqlcmd and now it's working exactly the same as executing it in Management Studio.
Thanks for looking!
March 19, 2013 at 7:57 am
It did (help someone else). I just encountered the same problem.
Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply