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

Actual exam question from Microsoft's 70-761
Question #: 1
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 the solution meet the goal?

  • A. Yes
  • B. No
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️
With X_ABORT ON the INSERT INTO statement and the transaction will be rolled back when an error is raised, it would then not be possible to ROLLBACK it again in the IF XACT_STATE() <> O ROLLBACK TRANSACTION statement.
Note: A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in the transaction it will be caughtant he transaction will be rolled back, finally an error 51000 will be raised.
Note: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
XACT_STATE is a scalar function thatreports 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 hasoccurred that has caused the transaction to be classified as an uncommittable transaction.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx
by avramov at Oct. 30, 2019, 6:30 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
indu07
Highly Voted 5 years, 3 months ago
B is the right answer because semicolon /terminator is not present after transaction statement
upvoted 8 times
...
tcroots19
Highly Voted 5 years, 2 months ago
So, think this is B for another reason: INSERT INTO Products(... ) has ProductPrice, but the tables defines UnitPrice ...so that should get Invalid Column name, right?
upvoted 5 times
...
Anirudh_net
Most Recent 3 years, 2 months ago
Answer is B
upvoted 1 times
...
NickNnack
4 years, 4 months ago
This question is basically a duplicate of question 1
upvoted 1 times
NickNnack
4 years, 4 months ago
Sorry, this is a duplicated (but with better text) in question 121
upvoted 1 times
...
...
Vermonster
4 years, 4 months ago
Answer is A assuming syntax is correct. See example C here - XACT_ABORT ON still passes errors to the CATCH: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15
upvoted 1 times
...
Andy7622
4 years, 5 months ago
There is incorrect XACT_STATE() value for the transaction that cannot be commited in the solution explanation . it must be '-1' instead of '2'
upvoted 1 times
...
Hoglet
4 years, 5 months ago
There are 2 errors in the Script with will cause it it fail at compilation. 1. A column is misnamed ProductPrice vs UnitPrice. 2. The line before a THROW must end in a semicolon. If both of these are corrected, then A is the correct answer. The effect of XACT_ABORT = ON is that XACT_STATE() will return -1 rather than 1 if there is a problem with the INSERT. You can ROLLBACK from either of these conditions. You cannot ROLLBACK to a savepoint when XACT_STATE() will returns -1, but not using a SavePoint in this case
upvoted 1 times
...
Andy7622
4 years, 6 months ago
I tried to reproduce the scripts with some corrections (consistent column names and semicolon) and I can say that this stored procedure works. Here are the scripts CREATE TABLE Products( ProductId int IDENTITY(1,1) NOT NULL PRIMARY KEY, ProductName nvarchar(100) NULL, UnitPrice decimal (18,2) NOT NULL, UnitsInStock int NOT NULL, UnitsOnOrder int NOT NULL); GO CREATE PROC InsertProducts @productname nvarchar(100), @unitprice decimal(18,2), @unitsinstock int, @unitonoder int AS BEGIN SET XACT_ABORT ON BEGIN TRY BEGIN TRAN INSERT INTO Products (ProductName, UnitPrice, UnitsInStock, UnitsOnOrder) VALUES (@productname, @unitprice, @unitsinstock, @unitonoder) COMMIT TRAN END TRY BEGIN CATCH IF XACT_STATE() <>0 ROLLBACK TRAN; THROW 51000, 'The product could not be created',1 END CATCH END; EXEC InsertProducts 'bike', 160.5, NULL, 2
upvoted 1 times
...
Billybob0604
4 years, 6 months ago
Answer is B for sure. 1. There's already a first thing that will fail. The insert statement lists columns that are not in the table DDL. You should use this : INSERT INTO PRODUCTS(PRODUCTNAME, unitprice, unitsinstock, unitsonorder) VALUES (@PRODUCTNAME, @UNITPRICE, @UNITSINSTOCK, @UNITSONORDER) 2. You shouldn't put the XACT_STATE() statement right before the THROW since it will make the THROW statement completely useless. XACT_STATE can be either -1 or 1. If the transaction is committable you get 1. If it isn't you get -1. So it will ALWAYS rollback. SS recognizes this and does not accept the THROW after this statement. You could do this : THROW 51000, 'The product could not be created', 1 IF XACT_STATE() = -1 ROLLBACK TRANSACTION
upvoted 1 times
...
Alsari
4 years, 8 months ago
The column names are different in the table and in the procedure. Table column names are UnitePrice, UniteStock etc, but in the procedure mentioned ProductPrice, ProductsInStock.
upvoted 1 times
...
TheDUdeu
4 years, 8 months ago
Look this will work with a ; on the IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; But like most questions on this site I think there are mistakes. On the test I believe they will not be testing you logic on a ; So beware if this question comes up makes sure you look at syntax juts in case. Should be B in this question.
upvoted 2 times
...
Minsire
4 years, 8 months ago
When set xact_abort is on and the transaction raises a runtime error the entire transaction is terminated and rolled back. However in this case the are syntax errors (incorrect column names and no semicolon after rollback transaction) that make the code to not run... Therefore the answer is B.
upvoted 1 times
...
Aghie
4 years, 9 months ago
definitely a NO, wrong column name.
upvoted 1 times
Andy7622
4 years, 6 months ago
people write these questions relying on their memories. And it might by the matter of wrong representation of what was remembered during the exam test. The size of the question is big and it isn't surprise to represent the question with mistakes like absent ';' or wrong column name
upvoted 1 times
...
...
trickytree
5 years ago
As others have pointed out, the column names in the stored procedure are wrong (e.g. ProductPrice rather than UnitPrice) and there is a missing terminator after Rollback Transaction. So, as it stands, it would not work but not for the reason stated? However, as @flashed has shown in their simplified, but syntactically correct, example, it would throw a 51000 error and rollback therefore meeting requirements? The inclusion of the SET XACT_ABORT ON statement suggests it is knowledge of this that is being tested rather than identifying the syntax errors. Anyone have a definitive answer? Is it just a poor question (that has been replicated on dozens of sites a quick google shows)?
upvoted 2 times
...
CristianCruz
5 years ago
answare is B table columns is productid, productname, unitprice, unitsinstock,unitsonorder and insert of procedure is productname,producprice,productsinstock,productsonorder columns not exists
upvoted 2 times
...
CristianCruz
5 years ago
answer is B. Before throw no statement terminator ; if XACT_STATE() <> 0 rollback transaction throw 51000, 'the product could not be created.',1 if XACT_STATE() <> 0 rollback transaction; throw 51000, 'the product could not be created.',1
upvoted 2 times
...
Hamburger
5 years, 1 month ago
Just tried @indu07 is right. It missed a terminator and failed to compile.
upvoted 1 times
...
Load full discussion...
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