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

Exam 70-762 All Questions

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

Exam 70-762 topic 1 question 129 discussion

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

HOTSPOT -
You are developing a stored procedure with the following requirements:
✑ Accepts an integer as input and inserts the value into a table.
✑ Ensures new transactions are committed as part of the outer transactions.
✑ Preserves existing transactions if the transaction in the procedure fails.
✑ If the transaction in the procedure fails, rollback the transaction.
How should you complete the procedure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
Box 1: @TranCount> 0 -
Procedure called when there is an active transaction. Create a savepoint to be able to roll back only the work done in the procedure if there is an error.

Box 2: @TranCount = 0 -
-- @TranCount = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.

Box 3: XACT_STATE() <> -1 -
If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-2017
by Bartek at Feb. 23, 2020, 9:55 p.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
Bartek
Highly Voted 5 years, 2 months ago
Box 2: No metter what You choosed. May be @@TRANCOUNT = 1 or @Trancount = 0
upvoted 6 times
RYP
5 years, 1 month ago
No, because at that point @@TRANCOUNT will be 1 or above, since we explicitly started a transaction if one wasn't already started. The point of the code where Box2 exists is checking "was there a transaction before I started? if not, we are safe to commit" to avoid committing transactions started outside the stored procedure. See: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver15
upvoted 2 times
JohnFan
5 years, 1 month ago
BEGIN CATCH IF @TranCounter = 0 ROLLBACK TRANSACTION; ELSE IF XACT_STATE() <> -1 ROLLBACK TRANSACTION ProcedureSave; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH
upvoted 6 times
Froze
5 years ago
Man stop putting irrelevant posts.
upvoted 7 times
Nickname17
5 years ago
I think that’s relevant, did you check the links provided?
upvoted 2 times
Nickname17
4 years, 11 months ago
It points out @Trancount = 0
upvoted 1 times
...
...
...
NhiN
4 years, 3 months ago
Thanks JohnFan. He posted the missing part of the Hot area
upvoted 1 times
NhiN
4 years, 3 months ago
The answer is correct (Box1 @trancount > 0 and Box 2 @trancount = 0) Please don't miss the local variable @trancount with @@trancount @Trancount = 0: no transaction in parent code @Trancount > 0: there is existing transaction in parent code Coding this way, we won't create nested transactions Please also be noted that the IF ELSE does not affect to multiple statement unless we put those statements in BEGIN .. END. That what stm22 confused with in his comment For box3 should be XACT_STATE() = -1 or XACT_STATE()>0, indicating that there is at least 1 active transaction and we need to rollback to the ProduceSave savepoint as Box3 only hit when @trancount> 0 (case of the store procedure was called within a existing transaction in parent code )
upvoted 1 times
NhiN
4 years, 3 months ago
Sorry I confused you with the XACT_STATE() = -1. It should be XAC_STATE()=1
upvoted 1 times
...
...
...
...
...
...
Andy7622
Most Recent 4 years, 3 months ago
what is the answer for box 3 in the end of all?
upvoted 1 times
...
Alex5x
4 years, 5 months ago
I agree with mikebondzio and upvoted his comment. If you have SET XACT_ABORT ON in the beginning of the SP, then you have no chance to rollback to a saved point because XACT_STATE() will be always -1 in case of any error. It means that either you change to SET XACT_ABORT OFF and play with save points or keep XACT_ABORT ON and forger about save points. In both cases you should notify the caller about the error, so you should write ;THROW; in your catch block.
upvoted 1 times
...
AmandaW
4 years, 5 months ago
this picture is only half. Does anyone know what's after the ELSE IF?
upvoted 1 times
...
mikebondzio
4 years, 7 months ago
But XACT_ABORT is on. So XACT_STATE can not have another state then -1 in the catch block. So the hole procedure make no sense!?!
upvoted 2 times
...
stm22
4 years, 9 months ago
The key to this is SET @TRANCOUNT = @@TRANCOUNT. When it fires, we have attempted no trans in our proc, so @TranCount > 0 means the caller had a trans already. And remember "Preserves existing transactions if the transaction in the procedure fails." Thus Box 1 is OK. it means if there is a caller tran, create a save point. Box 2: you can only hit Box 2 if the caller had no trans; it is in an Else that only fires if @TranCount = 0. so Box 2 cannot be @TranCount = 0. There is nothing in the Else that can change @TranCount, so it is always zero at Box 2. I think Box 2 should be "@@TRANCOUNT = 1" because that means the insert above Box 2 set @@TRANCOUNT = 1 and we must commit the insert. If that insert fails, the catch fires. if @TranCount = 0, then the caller had no trans and we rollback our failed trans. i can't see the rest, but the catch firing means our insert failed. if @TRANCOUNT > 0, then there is a caller tran to be preserved and we must commit back to the save point.
upvoted 2 times
...
raja1234567890
4 years, 11 months ago
Box2 should be @@TRANCOUNT = 1 and else block is executed only after @Trancount = 0
upvoted 2 times
Hoglet
4 years, 10 months ago
Incorrect. Box 2 has to be @Trancount = 0. Box 1 cannot test @@TRANCOUNT = 1 because it could be higher than 1 if multiple BEGIN TRANS were made before calling the stored procedure. So the test is @Trancount > 0 (as @Trancount current equals @@TRANCOUNT) Box 2 cannot test @@TRANCOUNT = 1 because we don't know of the transaction was started by us, or by the caller. Hence testing @Trancount = 0, we know that we started this transaction, so must commit it. Box 3, the only ELSE in the thing, and I can't see the options. However it will be ELSE IF XACT_STATE() <> -1 ROLLBACK TRANSACTION SavePoint Because of the preceeding If @Trancount = 0, we know the stored procedure was called within a Transaction, that we have a SavePoint and that we have to "Preserves existing transactions if the transaction in the procedure fails". As the procedure only does 1 action (INSERT Table1), we didn't really need the SavePoint, and if within the callers transaction do nothing in the event of a failure. However this savePoint is good coding practice, especially if someone comes to add to the procedure later.
upvoted 2 times
...
...
JohnFan
5 years, 1 month ago
In explicit transaction mode with nested transactions, each BEGIN TRANSACTION must correspond to a COMMIT TRANSACTION. As each new transaction starts with BEGIN TRANSACTION, the @@TRANCOUNT variable increments by 1 and each COMMIT TRANSACTION decrements it by 1. The complete transaction does not get written to disk and committed completely until @@TRANCOUNT is 0.
upvoted 1 times
JohnFan
5 years, 1 month ago
Remember that each BEGIN TRANSACTION increments the @@TRANCOUNT variable and each COMMIT TRANSACTION decrements it. The ROLLBACK TRANSACTION resets the variable to zero and rolls back every statement to the beginning of the first transaction, but does not abort the stored procedure. When @@TRANCOUNT is zero, SQL Server writes to the transaction log. If the session ends before @@TRANCOUNT returns to zero, SQL Server automatically rolls back the transaction.
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