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 |
|
aex
Yak Posting Veteran
60 Posts |
 Posted - 2006-05-31 : 05:05:57
|
| Hi, I have 3 stored proc, which proc 1 call proc2, and proc 2 call proc3. Each stored proc has its own transaction. My problem here is proc3 fails thus rollback happen in proc 3. However, when the proc3 fails regardless proc1 and proc2 are succeed, sql server complains:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.I did keep track of @@trancount and I find that @@trancount is set to 0 after the rollback executed in proc3. This is why sql server complain. So, I want to know how to solve this issue?Thanks for any reply.aex |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-05-31 : 05:10:39
|
| have you tried to use named transaction?Proc1: BEGIN TRAN lvl1Proc2: BEGIN TRAN lvl2Proc3: BEGIN TRAN lvl3 |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-31 : 05:16:46
|
| each procedure is a block in itself, imagine if you don't call them in one proc?so you get a begin tran and commit/rollback tran in each but receiving the error flag after calling the next proc--------------------keeping it simple... |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-05-31 : 19:54:35
|
| this means proc3 has a rollback statement that does not have a corresponding begin trans. This is possible when the code assumed a transaction, it will compile but once an error occurs, it will generate an error. Example:CREATE PROCEDURE Proc3begin(some SQL statement) if @@error <> 0 ROLLBACK -- without a corresponding begin transaction endMay the Almighty God bless us all! |
|
|
|
|
|
|
nested stored procedures transaction problem