Can run as a t-sql script but not as a procedure?

Question

Can run as a t-sql script but not as a procedure?

Sha_

Ten Centuries

Points: 1299
More actions
March 17, 2010 at 12:17 am

#145949

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

tyson.price SSCrazy Points: 2093 More actions · Answer 1

Not really knowing the issue one thing might be your connection properties in T-SQL are different then the database settings.

Paul White SSC Guru Points: 150467 More actions · Answer 2

Is there a SET XACT_ABORT command in the procedure?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sha_ Ten Centuries Points: 1299 More actions · Answer 3

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