sjoshi (sj****@ingr.com) writes:
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.
However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@@ERROR value. There are just DELETE statement and a plain RETURN.
In a normal stored procedure if you have:
BEGIN TRANSACTION
DELETE tbl WHERE ....
DELETE tbl2 WHERE ....
COMMIT TRANSACTION
you could very well end up with only one of the DELETE statements
being carried out, becuase many errors in SQL Server aborts the
current statment only.
A distributed transaction may be different. At least in some
situations there is a requirement that SET XACT_ABORT is ON. With
this settings all errors but compile errors abort the batch and
rollback the transaction.
But exactly what happens, I will have to admit that I don't know. The
best advice I can give is to test a scenario where one of the DELETE
statement fails. No, wait, the best is to either add checks on @@error
or make sure XACT_ABORT is ON.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
