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 66 discussion

Actual exam question from Microsoft's 70-762
Question #: 66
Topic #: 1
[All 70-762 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. Determine whether the solution meets the stated goals.
You need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.
You need to ensure that the stored procedure:
✑ Runs within a single transaction.
✑ Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
✑ Commits changes to all four tables when updates to all four tables are successful.
Solution: You create a stored procedure that includes the following Transact-SQL segment:

Does the solution meet the goal?

  • A. Yes
  • B. No
Show Suggested Answer Hide Answer
Suggested Answer: A 🗳️
Need to handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. We add the @CustomerComplete variable in the BEGIN TRY block, and test it in the BEGIN CATCH block.
Note: XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.
XACT_STATE =1: the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql
by Bartek at Feb. 5, 2020, 7:58 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
Anette
Highly Voted 4 years, 10 months ago
there are many problems in transaction. I think the answer is NO
upvoted 10 times
MML
4 years, 10 months ago
I aggree
upvoted 2 times
...
...
stm22
Highly Voted 4 years, 9 months ago
in the first IF statement in the catch they should have rollback tran tr1 (to get back to that point in the tran) commit tran (to commit at that point in the tran) but they didn't so answer = No
upvoted 9 times
...
Backy
Most Recent 4 years, 5 months ago
// The answer is B, one line should be added BEGIN CATCH IF (@CustomerComplete = 1) AND (XACT_STATE() = 1) BEGIN ROLLBACK TRAN TR1 // missing, added here COMMIT TRAN END ELSE IF XACT_STATE() = 1 ROLLBACK TRAN END CATCH
upvoted 1 times
...
Luzix
4 years, 5 months ago
END TRAN not works, it should be END TRY
upvoted 2 times
...
Alex5x
4 years, 6 months ago
The suggested solution is full of bugs. Let's start: 1) What does END TRAN mean? Obvioulsy, they wanted to write END TRY. 2) What is the point to write SAVE TRAN TR1 if you never use this save point in the code? They should somewhere in the the CATCH block write ROLLBACK TRAN TR1. 3) What will happen if any Update cause the transaction to become uncommittable (XACT_STATE() = -1)? The CATCH block does not handle this case at all.
upvoted 1 times
Alex5x
4 years, 6 months ago
Here is my version of their solution: SET NOCOUNT ON; SET XACT_ABORT OFF; DECLARE @CustomerComplete bit = 0; BEGIN TRY BEGIN TRAN; UPDATE Cutomer ... UPDATE CustomerInfo ... SET @CustomerComplete = 1; SAVE TRAN TR1; UPDATE OrderHeader ... UPDATE OrderDetails ... COMMIT TRANSACTION; END TRY BEGIN CATCH IF (@CustomerComplete = 1 AND XACT_STATE() = 1) -- caused by error when update OrderHeader or OrderDetails BEGIN ROLLBACK TRAN TR1; -- roll back to the savepoint. COMMIT TRAN; END ELSE IF XACT_STATE() <> 0 ROLLBACK TRAN; END CATCH
upvoted 7 times
Alex5x
4 years, 6 months ago
Alternative solution: SET NOCOUNT ON; SET XACT_ABORT OFF; DECLARE @RollbackPoint nchar(32) = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N''); BEGIN TRAN BEGIN TRY UPDATE Cutomer ... UPDATE CustomerInfo ... SAVE TRAN @rollbackPoint; BEGIN TRY UPDATE OrderHeader ... UPDATE OrderDetails ... END TRY BEGIN CATCH IF XACT_STATE() = 1 -- roll back to the savepoint. ROLLBACK TRAN @RollbackPoint; ELSE IF XACT_STATE() = -1 ROLLBACK TRAN; END CATCH IF XACT_STATE() = 1 COMMIT TRAN; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRAN; END CATCH
upvoted 1 times
...
...
...
Oooo
4 years, 6 months ago
stm22 is correct. The answer is no.
upvoted 1 times
...
Angelcr
4 years, 7 months ago
The correct answer is NO, because this line. IF (@customerComplete = 1) AND (XACT_STATE () = 1) It never enters the IF since in order for the table updates to be confirmed Customer and CustomerInfo have to be like this: IF (@customerComplete = 1) AND (XACT_STATE () = -1) In this way it meets all the requirements.
upvoted 3 times
...
Cococo
4 years, 7 months ago
65, 66 and 140 questions are part of the same subset. So, 65 and 140 are definitely NO. In 66 in the Catch if TR1 saved then commit it, ELSE if TR1 is not saved and one of the Updates (Customer or CustomerInfo) has died then XACT_STATE() can be 1 and we might want to rollback changes for one table only. I think this is YES, one question must be YES.
upvoted 1 times
kiri2020
4 years, 5 months ago
all 3 (65, 66 and 140) is no
upvoted 2 times
...
...
raja1234567890
4 years, 11 months ago
First rollback to save point needs to be happen. Else if failure in order detail table commits changes in order header table.
upvoted 4 times
...
JohnFan
5 years, 1 month ago
It is fine to write: IF XACT_STATE() = 1 ROLLBACK TRANSACTION But normally, it seems to write IF XACT_STATE() <> 0? As follows, BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; THROW; END CATCH
upvoted 2 times
...
Bartek
5 years, 3 months ago
I understand the concept, but do this exercise text make sense for You? the task contradicts itself. 1. " Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables." We need to update at least Customer and CustomerInfo tables ; this is ok 2." Commits changes to all four tables when updates to all four tables are successful." So.. we need update two tables mentioned above at least or we have to update all ? Idk;p II be nice to meet You opinion guys
upvoted 1 times
Twigit
5 years, 2 months ago
You have the right idea. 1. Customer and CustomerInfo should ALWAYS be updated, as long as neither of them fail. 2. If all four tables update successfully changes for all 4 tables are committed. Using the checkpoint in the answer accomplishes this goal.
upvoted 3 times
JohnFan
5 years, 1 month ago
You have the right idea. The code is misleading: 1. It is END TRY rather than END TRAN; 2. It is ROLLBACK TR 1 instead of ROLLBACK TRAN.
upvoted 3 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