Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 129 discussion

Actual exam question from Microsoft's 70-762

Question #: 129
Topic #: 1

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

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

Submit Cancel

Bartek

Highly Voted 5 years, 4 months ago

Box 2: No metter what You choosed. May be @@TRANCOUNT = 1 or @Trancount = 0

upvoted 6 times

RYP

5 years, 3 months 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, 3 months 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, 2 months ago

Man stop putting irrelevant posts.

upvoted 7 times

Nickname17

5 years, 2 months ago

I think that’s relevant, did you check the links provided?

upvoted 2 times

Nickname17

5 years, 1 month ago

It points out @Trancount = 0

upvoted 1 times

...

NhiN

4 years, 5 months ago

Thanks JohnFan. He posted the missing part of the Hot area

upvoted 1 times

NhiN

4 years, 5 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, 5 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, 5 months ago

what is the answer for box 3 in the end of all?

upvoted 1 times

...

Alex5x

4 years, 7 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, 7 months ago

this picture is only half. Does anyone know what's after the ELSE IF?

upvoted 1 times

...

mikebondzio

4 years, 9 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, 11 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

5 years ago

Box2 should be @@TRANCOUNT = 1 and else block is executed only after @Trancount = 0

upvoted 2 times

Hoglet

5 years 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, 3 months 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, 3 months 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

...