70-465 Question #65: Real Exam Question with Answer & Explanation

Question

You need to recommend a change to USP_3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails. Which change should you recommend?

Options

• ASet the XACT_ABORT option to off.
• BSet the XACT_ABORT option to on.
• CSet the IMPLICIT_TRANSACTIONS option to off.
• DSet the IMPLICIT_TRANSACTIONS option to on.

Explanation

This question tests knowledge of SQL Server session options that control transaction behavior when a runtime error occurs. Specifically, XACT_ABORT determines whether a transaction is automatically rolled back when a statement error occurs.

Common mistakes.

• B. Setting XACT_ABORT to ON causes SQL Server to automatically roll back the entire current transaction and terminate the batch whenever a run-time error occurs, which would stop execution of the procedure rather than allowing it to continue after a failed UPDATE.
• C. IMPLICIT_TRANSACTIONS OFF means each statement runs in autocommit mode unless explicitly wrapped in a transaction, which does not directly address whether the procedure continues executing after an individual UPDATE statement fails.
• D. Setting IMPLICIT_TRANSACTIONS to ON causes SQL Server to automatically start a transaction for DML statements, but this does not allow the procedure to continue executing after a failed UPDATE; it controls transaction scope, not error-continuation behavior.

Concept tested. SQL Server XACT_ABORT session option error handling

Reference. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

No community discussion yet for this question.