Exam 70-461 topic 1 question 197 discussion

Actual exam question from Microsoft's 70-461

Question #: 197
Topic #: 1

A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements.
The stored procedure must use transaction management to handle errors.
You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs.
Which Transact-SQL statement should you add to the stored procedure?

A. SET ARITHABORT ON
B. SET NOEXEC ON
C. SET TRANSACTION ISOLATION LEVEL ON
D. SET XACT_ABORT ON

Show Suggested Answer

Suggested Answer: D 🗳️
SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

by [deleted] at April 4, 2025, 11:17 a.m.

Comments

Submit Cancel

Currently there are no comments in this discussion, be the first to comment!