Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Swoosh
Starting Member
6 Posts |
 Posted - 2004-02-14 : 19:09:29
|
| I created two stored procedures, A & B for simplicity: "A" creates 1 record in a table. It's wrapped in a BEGIN TRANSACTION so if any errors occur, it gets rolled back. "B" also creates 1 record in a table and is also wrapped in a BEGIN TRANSACTION so if any errors occur, it gets rolled back. However, "B" makes 2 calls to "A".When I call "B" and an error occurs inside of "A", a rollback is done inside "A" which throws the @@TRANCOUNT out of synch. Then when "A" returns back to "B", I get the following error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Is there any way to prevent this, possibly with SAVE points? |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-02-15 : 00:39:42
|
| Nested transactions don't quite work that way. A ROLLBACK TRAN issued in the inner transaction will rollback all transactions in that "nest" including the outer transaction as well. So when you attempt to rollback the outer transaction, SQL Server throws the error saying "hey, I don't have any more transactions left to rollback". Read more about it in the Books Online. Also see: http://sqlteam.com/forums/topic.asp?TOPIC_ID=31940OS |
 |
|
|
|
|
|
Nested Stored Procedures