MSSQLTips

 I am having doubt - is it achivable Or not? The senario is ,
"If the outer transaction is rolled back, then all inner transactions are also rolled back"
is it any way to avoid Rollbacking of inner transactions??

Thanks,

srinivasan r 

Your are correct that if the outer transaction is rolled back all of the inner transactions are also rolled back.

 Yes.but My Question is - "is there anyway to avoid Rollbacking of inner transactions alone??"

There does not seem to be a way to do this.

If I understand your question correctly, you can achieve this using savepoints 

 if object_id('mytable') is not null
   drop table mytable
go
create table mytable (mycol char(1))
go

-- Outer tran
begin tran tran1
insert mytable select '1'
save tran save1

   -- inner tran
   begin tran tran2
   insert mytable select '2'
   save tran save2

     -- another inner tran
     begin tran tran3
     insert mytable select '3'
     save tran save3
    

-- some error occurred; rollback up to savepoint 2
rollback tran save2


select open_tran from master.dbo.sysprocesses where spid = @@spid

-- commit all work [Amended to close all open transactions]
if @@trancount <> 0
begin
  commit tran tran1
  commit tran tran2
  commit tran tran3
end

select * from mytable
select open_tran from master.dbo.sysprocesses where spid = @@spid

 Hi aprato,

           Thanks for your reply. i will try this out.