Re: TRANSACTIONS in a WHILE LOOP. Flow Question
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 8 May 2006 21:41:20 +0000 (UTC)
rlm (groups@xxxxxxxxxxx) writes:
When I attempt to manage Transactions in a WHILE LOOP @@TRANCOUNT is
off. I obviously do not understand error handling as I should. In the
loop below where does the point of execution move to after an error?
RESULT:
Server: Msg 266, Level 16, State 1, Procedure flowcontrol_test, Line
111
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0, current count =
1.
If I understand this correctly, you get an error here:
-- X Doesn't Exist. Where does the point of execution go???
UPDATE X
SET update_dtm = @dateTimeStamp
WHERE v = @V
Answer: to the next line in the *calling* procedure.
Error handling in SQL Server 2000 is a fairly messy story, and you cannot
always catch an error that occurs in a procedure in the procedure itself -
or even in T-SQL at all.
I have two articles on my web site on this:
http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html
It's a lot better in SQL 2005.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Prev by Date: Merge replication issue - one column not replicating
- Next by Date: where do SQL Jobs live?
- Previous by thread: TRANSACTIONS in a WHILE LOOP. Flow Question
- Next by thread: C# / Sql Server question
- Index(es):
Relevant Pages
|
Loading
