Categories
loops sql transactions tsql

Cannot roll back subtransaction. No transaction or savepoint of that name was found

i have a loop while in sql which do something as it

    begin tran one
do some inserts in others tables
--start loop
begin tran two
--do something
begin try
--if something fail then a trigger does rollback and this return a error (and this goes to catch), then don't i need do the rollbak in catch? this could not be dissable because this is working on production
--something finished ok
commit tran two
end try
begin catch
rollback tran two
end catch
--finished loop
commit
----------

i got this error

Uncommittable transaction is detected at the end of the batch. The
transaction is rolled back.

begin tran one
begin tran two
rollback tran two

doing this code i get this:

Cannot roll back two. No transaction or savepoint of that name was found.

I only want the subquery to rollback the second loop and continue with others records.