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

Operator rollback rolls back all transaction, for roll back only second loop you you must use savepoints:

  begin tran one-- do some inserts in others tables  --start loop  save tran two -- begin tran two  --do something  begin try     update product set id = 1 --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 loopcommit

trigger example:

create table product (id int)GO  create trigger product_trigger on product for updateas  set xact_abort off  if (select count(*) from inserted i join product p on i.id=p.id)=0 begin     if (@@trancount>0) begin       /* rollback */       raiserror('product does not exist', 16, 1)     end   end

In my case, was my code was calling, thru an EF DbContext method, a SQL Server stored procedure, which contained a non-nested transaction.

Since, as @NotMe has already pointed-out, that "there is no such as a nested transaction in SQL Server", I began wondering whether my process was really transaction-nestingless.

Suspecting, that my DbContext had some guilt, I started checking on DbContext options, until DbContext.Configuration.EnsureTransactionsForFunctionsAndCommands = True caught my attention.

So, as soon as I changed it value to True, everything worked successfully.

MyDbContext.Configuration.EnsureTransactionsForFunctionsAndCommands = false;

What happened?

Well, in my opinion, EF's ObjectContext.ExecuteFunction method was managing its own outer transaction as a wrapper to my stored procedure's inner transaction, so, when my stored procedure's ROLLBACK TRAN was hit, there was no pending transaction when EF's COMMIT/ROLLBACK code was hit.

Oddly enough, while gathering some references on EnsureTransactionsForFunctionsAndCommands property, I found that this default behaviour is due to one of the worst (in my opinion) EF team's decision ever, since it collides diretly with every ROLLBACK TRAN inside a T-SQL script.

For further details on EF, check insightfull SO's QA at EF6 wraps every single stored procedure call in its own transaction. How to prevent this?

Basically, everyone should check @@trancount > 0 before issuing a ROLLBACK command, whether named or not, specially inside stored procedure.

CREATE PROCEDURE Proc1 ASBEGIN    BEGIN TRAN    EXEC Proc2    IF(@@trancount > 0)        COMMIT TRANENDCREATE PROCEDURE Proc2 ASBEGIN    BEGIN TRAN    ROLLBACK TRANEND

For better awareness about Microsoft SQL Server's nested transactions, I would suggest reading the following articleBe careful using ROLLBACK on nested transaction in SQL Server!

Hope it helps someone :-)