Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 122 discussion

Actual exam question from Microsoft's 70-761

Question #: 122
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 🗳️

by Bartek at Sept. 19, 2019, 8:22 p.m.

Comments

Submit Cancel

mattia_88

Highly Voted 5 years, 8 months ago

THE CORRECT ANSWER IS "A" ("YES"). BUT BEFORE YOU MUST CREATED MESSAGE WITH CODE 51000 (SP_ADDMESSAGE 51000,16,'ERROR')

upvoted 5 times

...

Billybob0604

Most Recent 4 years, 5 months ago

To me it remains a mystery why you have to end a throw statement like in the previous question with a semicolon and a raiserror apperently doesn't require one.

upvoted 1 times

...

PHaringsNL

4 years, 5 months ago

Although the procedure works, the question is to Throw the 51000 error which has not been created with the things said to be executed. So it would be B. No. If this was created it would work. I've tried executing both and it works

upvoted 1 times

...

Vermonster

4 years, 5 months ago

Works as long as error message 51000 is defined by sp_addmessage

upvoted 1 times

...

Andy7622

4 years, 6 months ago

it works

upvoted 1 times

...

getstoopid

4 years, 10 months ago

I think it is A ('YES') - couldn't think of any error that would actually prevent the rollback if the insert statement was executed and failed

upvoted 1 times

...

JonoBraans

5 years, 3 months ago

Please explain why this is B

upvoted 1 times

xd1

5 years, 2 months ago

XACT_ABORT is off, so if something goes wrong its not guaranteed to actually be caught by the catch block

upvoted 3 times

Hoglet

4 years, 6 months ago

Disagree with xd1. Anything that would cause XACT_ABORT to "trigger" would also be caught by a TRY/BATCH block

upvoted 2 times

...

Jiacheng

5 years, 4 months ago

@@TRANCOUNT>0 cannot make sure that all error would go to CATCH block, check the following code, I add string to int on purpose, it can't trigger catch block CREATE TABLE #Products( ProductID INT IDENTITY NOT NULL PRIMARY KEY, productName VARCHAR(100) NULL, UnitPrice DECIMAL(18,2) NOT NULL, UnitInStock INT NOT NULL, UnitsOnOrder INT NULL ) CREATE PROC InsertProduct @ProductName varchar(100), @UnitPrice decimal(18,2), @UnitsInStock int, @UnitsOnOrder INT AS BEGIN BEGIN TRY BEGIN TRANSACTION INSERT INTO #Products (productName,UnitPrice,UnitInStock,UnitsOnOrder) VALUES (@ProductName,@UnitPrice,@UnitsInStock,@UnitsOnOrder) COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT>0 ROLLBACK TRANSACTION RAISERROR(51000,16,1) END CATCH END EXEC InsertProduct 'Book',30,'LOL',3 SELECT * FROM #Products

upvoted 4 times

Anette

5 years ago

So, you mean the answer is NO?

upvoted 2 times

...

getstoopid

4 years, 10 months ago

You create an error actually before calling the sp itself so what error should the catch block catch?

upvoted 1 times

...

Deimy

4 years, 9 months ago

if you invoke your stored procedure let's say EXEC InsertProduct 'Book',30,NULL,3 it actually caught by the catch block with raise the error.

upvoted 1 times

...

Hoglet

4 years, 6 months ago

I think this is a pointless example. The stored procedure will fail to execute, not that the insert will fail and need to be handled.

upvoted 1 times

...

raja1234567890

5 years, 7 months ago

By defaut XABORT will be OFF. https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15

upvoted 2 times

Hoglet

4 years, 6 months ago

While this statement is correct, you don't give any clue as to why you think it's relevant. TRY/CATCH will work regardless of the XABORT setting

upvoted 1 times

Billybob0604

4 years, 5 months ago

unless you make a compile error such as selecting from a non existing table

upvoted 1 times

...

Bartek

5 years, 9 months ago

do not know why this should not work. @@TRANCOUNT is equal to 1 in case of error occurs. Only thing is that the ErrorMsg is not assigned to 51000 ErrorId...

upvoted 4 times

...