Exam 70-761 topic 1 question 2 discussion

Actual exam question from Microsoft's 70-761

Question #: 2
Topic #: 1

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

Suggested Answer: B 🗳️
A transaction is correctly defined for the INSERT INTO .VALUES statement, and if there is an error in the transaction it will be caught ant he transaction will be rolled back. However, error number 51000 will not be returned, as it is only used in an IF @ERROR = 51000 statement.
Note: @@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
References:
https://msdn.microsoft.com/en-us/library/ms187967.aspx

by Robintang0924 at Jan. 6, 2020, 11:20 p.m.

Comments

Submit Cancel

Anirudh_net

3 years, 3 months ago

Answer is B

upvoted 1 times

...

Vermonster

4 years, 5 months ago

Agree on answer B since 51000 won't be returned as the value. @@error also used incorrectly

upvoted 1 times

...

kyliek7

4 years, 6 months ago

Anyway, I have created this logic in MSSMS and this procedure will show you as an effect that there was 0 rows affected and error won't be shown definitely B

upvoted 1 times

...

CristianCruz

5 years, 1 month ago

answare is B table columns is productid, productname, unitprice, unitsinstock,unitsonorder and insert of procedure is productname,producprice,productsinstock,productsonorder columns not exists And @@error check it immediately following the statement being verified, or save it to a local variable that can be checked later.

upvoted 2 times

Andy7622

4 years, 7 months ago

I think you should not pay attention to this. It isn't the point of the test question , just a mistake of the website copywriter

upvoted 1 times

Andy7622

4 years, 7 months ago

I meant the column names

upvoted 1 times

...

Robintang0924

5 years, 5 months ago

btw: @@error only makes sense when you capture/save it to variable IMMEDIATELY after statement that might raise error. it will be reset even in IF statement, so in this case ERROR_NUMBER function is the best choice if you really want to check some error in catch block. https://docs.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql?view=sql-server-ver15

upvoted 3 times

...

Robintang0924

5 years, 5 months ago

It doesn't meet the requirement as explanation of answer, below 2 links could be used to reference: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15#uncommittable-transactions-and-xact_state https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql?view=sql-server-ver15

upvoted 1 times

...