You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is
terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
A.
SET XACT_ABORT ON
B.
SET ARITHABORT ON
C.
TRY
D.
BEGIN
E.
SET ARITHABORT OFF
F.
SET XACT_ABORT OFF
Explanation:
http://msdn.microsoft.com/en-us/library/ms190306.aspx
http://msdn.microsoft.com/en-us/library/ms188792.aspx
Answer is A.
?
=> When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
=> When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
So the answer is ‘A’.
A
A