MS SQL server Transaction commit rollback

MS SQL server Transaction commit rollback

To know your transaction is getting rolled back you need to generate an exception.

This will ensure that transcation is rolled back.

Trick is very simple, just write statement select 1/0 at the end bo transaction block as shown in below query.

select 1/0 will generate an exception and your transaction needs to be rolled back.

Begin transaction MyTransactionName

begin try

-- Delete some rows from sql server
-- insert deleted rows into other table

select 1/0  -- This will generate an exception and your transaction needs to be rolled back

commit transaction MyTransactionName

End try

begin catch

rollback transaction MyTransactionName

end catch

END

If you are using linked server, you need to enable MSDTC services (Microsoft distributed transaction co-ordinates).

MS SQL server Transaction commit rollbackMS SQL server shutdownOLE DB provider SQLNCLI for linked server returned message The partner transaction manager has disabled its support for remotenetwork transactionsError The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B The Transaction Manager is not available The DTC transaction failed to start This could occur because the MSDTC Service is not runninggodaddy hosting MS SQL server configurationtransactionMS SQL server service packOLE DB provider SQLNCLI for linked server returned message Cannot start more transactions on this sessionMS SQL server mirroring remove end points master keyConnection PoolingMS SQL server 2005 attach databse without ldf file

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.