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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy