will SetAbort() rollback transactions even I have a commit in my procedures?
I am writing some code to test the possiblity to use COM+ enterprise transaction to rollback procedures in our DB tests. So for each test, begin a transaction, then run stored procedures, then call SetAbort() to rollback everything. Then run the next test.
Our stored procedures are all have transactions inside them, for example, Oracle procedures has commit in the end of the stored procedures, MSSQL procedures has COMMIT TRANSACTION in the end of the stored procedures.
However, when I run the test code against Oracle, the SetAbort() seems didn't rollback, is it because of the commit in the procedure? When I run the test against MSSQL Server, I get a 'cannot save transaction within a distributed transaction' exception. What is it mean?
Does it means the SetAbort() can't rollback those transactions inside stored procedures?
Any advices are welcome, cheers
There is a article I read(http://msdn.microsoft.com/msdnmag/issues/0600/advbasics/) said:
"Stored Procedures and Components
Many current systems already have lots of stored procedures in them, and many of these stored procedures have been written with transactions. If your system uses transactional stored procedures and you want to invoke those stored procedures from transactional MTS and COM+ objects, what will happen? For example, say I have an MTS and COM+ component. It is marked as Transaction Required, and it calls a stored procedure that starts a transaction via BEGIN TRAN. The component then performs some inserts or updates or deletes, and then calls ROLLBACK TRAN. Does the MTS and COM+ transaction get aborted? Is calling ROLLBACK TRAN in a stored procedure similar to calling SetAbort in a transactional component?
The answer is yes, the transaction gets rolled back and all work being done in MTS and COM+ would also get rolled back. So, the ROLLBACK TRAN statement acts like a SetAbort call. However, calls within your stored procedure to BEGIN TRAN and COMMIT TRAN are simply ignored—a call to BEGIN TRAN does not actually start another transaction, and calls to COMMIT TRAN do not actually commit the transaction. The transaction cannot be committed until the MTS and COM+ root object gives the final vote via SetAbort or SetComplete—then the DTC goes about its work to handle the transaction's outcome.
This is important to know because if you mix your existing transactional logic in stored procedures with transactional MTS and COM+ components, you need to know how they are going to interact. Besides, if you have several hundred or even thousands of stored procedures that are transactional, you may not want to rewrite them just so you can use MTS and COM+. You should test for yourself to make sure all of them are going to work as expected. If you are creating a system from scratch and you intend to use MTS and COM+, you are obviously free to leave the transactional logic in your components instead of putting it in both the stored procedure and your components.
"
