Exam 70-464 topic 1 question 13 discussion

Actual exam question from Microsoft's 70-464

Question #: 13
Topic #: 1

You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
✑ Either BOTH statements succeed or BOTH statements fail as a batch.
✑ If an error occurs on the first statement, SQL should not attempt to run the second statement.
✑ Error information should be returned to the client.
What should you do?

A. Option A
B. Option B
C. Option C
D. Option D

Show Suggested Answer

Suggested Answer: A 🗳️
SET XACT_ABORT -
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
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.

by humba at May 22, 2020, 10:12 a.m.

Comments

Submit Cancel

Luzix

4 years, 7 months ago

I meets humba. Option C is the correct due to the Option A does not shows error message.

upvoted 1 times

Kepty

4 years, 6 months ago

However, in C the commit is in the catch, so the transaction remains open...

upvoted 2 times

...

Luzix

4 years, 7 months ago

SET XACT_ABORT ON SHOWS AN ERROR MESSAGE. Check with the following example code: IF OBJECT_ID(N't2', N'U') IS NOT NULL DROP TABLE t2; GO IF OBJECT_ID(N't1', N'U') IS NOT NULL DROP TABLE t1; GO CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY); CREATE TABLE t2 (a INT NOT NULL REFERENCES t1(a)); GO INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (3); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (6); GO SET XACT_ABORT OFF; GO BEGIN TRANSACTION; INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (2); -- Foreign key error. INSERT INTO t2 VALUES (3); COMMIT TRANSACTION; GO SET XACT_ABORT ON; GO BEGIN TRANSACTION; INSERT INTO t2 VALUES (4); INSERT INTO t2 VALUES (5); -- Foreign key error. INSERT INTO t2 VALUES (6); COMMIT TRANSACTION; GO -- SELECT shows only keys 1 and 3 added. -- Key 2 insert failed and was rolled back, but -- XACT_ABORT was OFF and rest of transaction -- succeeded. -- Key 5 insert error with XACT_ABORT ON caused -- all of the second transaction to roll back. SELECT * FROM t2; GO

upvoted 1 times

...

humba

5 years ago

option A does not return an error message to client and I think the correct answer is C considering the fact that it returns an error message and statement1 and statement2 are in one transaction which is rolled back if any error is found in the catch clause

upvoted 2 times

...