March 17, 2010 at 12:17 am
Hi all,
I have a cursor that one of the rows will encounter an expected error. When I run this in Query Analyzer, the error surfaces, but the cursor will continue on to fetching the next statement, execute the commands, and so on.
If I take this same script and make a stored procedure out of it, then when the expected error surfaces the remaining rows in the cursor do not get processed. Instead, the procedure fails at that point.
Why is there a difference in failure behavior\completion behavior when you run a script in t-sql versus stored procedures?
Thanks in advance,
Sharon
March 17, 2010 at 3:43 am
Not really knowing the issue one thing might be your connection properties in T-SQL are different then the database settings.
March 17, 2010 at 4:21 am
Is there a SET XACT_ABORT command in the procedure?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 17, 2010 at 6:39 am
Hi all,
Thanks for your replies. I've done some more testing and I realize I am not correct in my understanding and reporting of the problem. The accurate description is that when I run a proc via an Agent job, then the point of failure is different than when I run the contents of that same proc in a Query Analyzer window. When kicked off by the job, it fails at first error, versus running that same proc in QA will complete and fail at the end (as it is written to do - save the @rc value until the end).
I've been caught by this before, and I believe I posted the same problem about a year ago. I will search for that answer since I don't recall it. If anyone knows why there's a difference in error handling behavior from the Agent, I'd love to understand why...
Thanks again for your replies,
Sharon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply