ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-761 All Questions

View all questions & answers for the 70-761 exam
Go to Exam

Exam 70-761 topic 1 question 187 discussion

Actual exam question from Microsoft's 70-761
Question #: 187
Topic #: 1
[All 70-761 Questions]

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?

  • A. Yes
  • B. No
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️
XACT_STATE is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
The states of XACT_STATE are:
✑ 0 There is no active user transaction for the current request.
✑ 1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
✑ 2 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.
Example of correct use:

BEGIN CATCH -
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.

IF (XACT_STATE()) = -1 -

BEGIN -
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.

IF (XACT_STATE()) = 1 -

BEGIN -
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx
by M4x at Sept. 16, 2019, 11:39 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
M4x
Highly Voted 5 years, 7 months ago
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.
upvoted 16 times
kiri2020
4 years, 6 months ago
Although the transaction gets rolled back with XACT_ABORT and it woks to satisfy requirements without coding rollback, several Microsoft links show code for rolling back transaction for XACT_STATE = -1 - see here - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15 And here - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15#uncommittable-transactions-and-xact_state - And here - https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-ver15 Are you saying these are all wrong?
upvoted 1 times
...
...
vermeilyn
Highly Voted 4 years, 11 months ago
When you use XACT_STATE ON, THROW will roll back the transaction even if there is no ROLL BACK TRANS clause!
upvoted 7 times
...
Billybob0604
Most Recent 4 years, 3 months ago
M4x is right. Answer A is correct.
upvoted 1 times
...
stm22
4 years, 9 months ago
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.
upvoted 3 times
BabyBee
4 years, 4 months ago
If you print it before ;THROW yes, there will be open transaction, but you run it again after ;throw and it returns 0. ;THROW will rollback the transactions. Reading the Querying book only setting XACT_ABORT ON will do the trick in a batch with a BEGIN TRAN, and since there is a TRY CATCH block the error will jump to CATCH and THROW will rollback.
upvoted 1 times
...
...
stm22
4 years, 9 months ago
i tried it too: (0 rows affected) Msg 51000, Level 16, State 1, Line 30 MANUAL MSG ERROR answer = Yes
upvoted 1 times
...
Famerin
4 years, 11 months ago
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.
upvoted 1 times
jonasdv
4 years, 6 months ago
not true, when xact_abort is on, a throw will abort transaction and automatically roll back.
upvoted 1 times
miep
3 years, 7 months ago
this is not true: xact_abort will only rollback a transaction when the error happens outside of the try block: it does not catch errors inside the tryblock
upvoted 1 times
...
...
...
flashed
5 years, 2 months ago
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
upvoted 6 times
Backy
4 years, 7 months ago
The transaction is not completed but it does not mean it has been rolled back - the question asks to roll it back. In your example, the transaction is left hanging in error state
upvoted 1 times
...
...
fabzo
5 years, 5 months ago
answer is NO as it will not be possible with XACT on to return error 51000
upvoted 3 times
eggzamtaker
4 years, 3 months ago
false. you are wrong. the correct answer is "YES".
upvoted 1 times
...
...
Bartek
5 years, 7 months ago
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.
upvoted 4 times
anonimdom
5 years, 1 month ago
Did you test it? "None of the remaining code in the transaction is executed." - that might mean the remaining code in begin tran...commit tran.
upvoted 1 times
...
xd1
5 years ago
XACT abort works different than what you are describing if you are using a TRY/CATCH statement. XACT abort icw try/catch just throws everything to catch; so this works just fine.
upvoted 1 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago