70-761 Exam - Question 187

Question

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You create a table named Products by running the following Transact-SQL statement:

You have the following stored procedure:

You need to modify the stored procedure to meet the following new requirements:

✑ Insert product records as a single unit of work.

✑ Return error number 51000 when a product fails to insert into the database.

✑ If a product record insert operation fails, the product information must not be permanently written to the database.

Solution: You run the following Transact-SQL statement:

Does this meet the goal?

Examice · Accepted Answer

The provided solution meets the requirements. By setting XACT_ABORT ON, any error encountered in the TRY block will cause the transaction to be rolled back automatically, handling the requirement that the product information must not be permanently written to the database if a failure occurs. The THROW statement inside the CATCH block correctly returns the error number 51000 when an error occurs, addressing the need to return a specific error number. Hence, the solution ensures the insertion of product records as a single unit of work and maintains database integrity by rolling back the transaction in case of failure.

M4x · Answer

Wrong ! The explanation says about XACT_STATE but the firs line of code is XACT_ABORT that rollback in case of any error. Correct answer YES.

vermeilyn · Answer

When you use XACT_STATE ON, THROW will roll back the transaction even if there is no ROLL BACK TRANS clause!

flashed · Answer

I agree with M4x, the correct answer is YES. I tested:
CREATE TABLE [dbo].[DISTRICTS](
	[ID_District] [int] IDENTITY(1,1) NOT NULL,
	[DistrictName] [varchar](255) NOT NULL,
	[City] [varchar](2) NULL
) ON [PRIMARY]
GO

BEGIN
SET XACT_ABORT ON
	BEGIN TRY
		BEGIN TRANSACTION   
			INSERT INTO DISTRICTS (DISTRICTNAME,CITY)
			VALUES ('LIS', 'TRETRT')
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		THROW 51000, 'MANUAL MSG ERROR',1
	END CATCH
END
;

error message:
(0 rows affected)
Msg 51000, Level 16, State 1, Line 10
MANUAL MSG ERROR

Bartek · Answer

Correct answer = "No"

There is another option for trapping errors that is one step toward structured error handling:
SET XACT_ABORT (where XACT stands for "transaction"). XACT_ABORT works with all types
of code and affects the entire batch. You can make an entire batch fail if any error occurs by
beginning it with SET XACT_ABORT ON. You set XACT_ABORT per session. After it is set to
ON, all remaining transactions in that setting are subject to it until it is set to OFF.
SET XACT_ABORT has some advantages. It causes a transaction to roll back based on any
error with severity > 10. However, XACT_ABORT has many limitations, such as the following:
■■ You cannot trap for the error or capture the error number.
■■ Any error with severity level > 10 causes the transaction to roll back.
■■ None of the remaining code in the transaction is executed. Even the final PRINT statements
of the transaction are not executed.
■■ After the transaction is aborted, you can only infer what statements failed by inspecting
the error message returned to the client by SQL Server.

fabzo · Answer

answer is NO as it will not be possible with XACT on to return error 51000

stm22 · Answer

if you copy flash's example, and replace the contents of the catch with:
print XACT_STATE();
print @@TRANCOUNT

running it yields:
(0 rows affected)
-1
1
Msg 3998, Level 16, State 1, Line 84
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Famerin · Answer

The answer (B) is correct, but the explenation is not. What is missing is an actual ROLLBACK statement in the CATCH block (in whichever form you prefer it). Without an explicit ROLLBACK, a transaction would never be rolled back. Hence, unwanted changes to the database could occur.

stm22 · Answer

i tried it too:

(0 rows affected)
Msg 51000, Level 16, State 1, Line 30
MANUAL MSG ERROR

answer =  Yes

Billybob0604 · Answer

M4x is right. Answer A is correct.

70-761 Exam - Question 187

Discussion