July 20, 2004 at 2:13 pm
Here is my problem.. lets say I have a procedure
create procedure spProc1
@Parameter int = 0
as
declare
@ReturnCode int
begin transaction tran1
IF (@Parameter = 2) BEGIN
SET @ReturnCode = 1
GOTO ABORT_TRANSACTION
END
commit transaction tran1
set @ReturnCode = 0
GOTO END_PROCEDURE
ABORT_TRANSACTION:
ROLLBACK TRANSACTION tran1
END_PROCEDURE:
RETURN @ReturnCode
Ok so there is a transaction in that procedure
Now I have another procedure that will call the procedure above
create procedure spProc2
as
declare
@ReturnCode int
begin transaction tran2
exec @ReturnCode = spProc1 2
IF (@ReturnCode != 0) BEGIN
SET @ReturnCode = 1
GOTO ABORT_TRANSACTION
END
commit transaction tran2
set @ReturnCode = 0
GOTO END_PROCEDURE
ABORT_TRANSACTION:
ROLLBACK TRANSACTION tran2
END_PROCEDURE:
RETURN @ReturnCode
Now for some reason I get the following error:
Cannot roll back tran1. No transaction or savepoint of that name was found. I have read through the documentation about nested transactions but am still not sure why this is failing
any help would be great
thanks
Will
July 20, 2004 at 9:08 pm
I think the problem might be the fact that the tran1 doesn't really do anything in the example. Have you tried having it INSERT a record into a "play" table with a single field with GETDATE() and see if you get the message?
I think the message is saying I either found tran1 and it didn't do anything so I can't rollback OR I didn't find tran1 and I don't know what you want me to do...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 21, 2004 at 5:27 pm
It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all the nested transactions, including the outermost transaction.
bondada
July 21, 2004 at 5:33 pm
bondada,
I think the problem lies with the call to the 1st sp. Inside the 1st sp is where the problem comes in....... I think it is because the trans didn't do anything to be rolled back....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 22, 2004 at 1:41 pm
Will,
in your spProc2 do this:
ABORT_TRANSACTION:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
BTW, stop using GOTO
Igor
July 28, 2004 at 3:54 am
what about writing
Begin TRANSACTION
and
Commit TRANSACTION
and
ROLLBACK TRANSACTION
without giving name and transaction (Trans1)
and in inner Transaction .. just make an outer parameter (@err) that is 1 if Roll back happened .. so outer Transaction make Rollback
i make something like this in my Aplication and it worked well.
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
August 30, 2004 at 1:58 pm
The problem is SQL server itself. You cannot really use nested transactions in T-SQL.
If you rollback - you rollback everything up to the first transaction you started in this batch.
That's in Oracle you can rollback any nested transaction right to the point where it started - but not in SQL server.
Alex
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply