Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 121 discussion

Actual exam question from Microsoft's 70-761

Question #: 121
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 Ilray at Sept. 18, 2019, 10:47 a.m.

Comments

Submit Cancel

Ilray

Highly Voted 5 years, 9 months ago

If after ROLLBACK TRANSACTION put a semicolon then A is correctly

upvoted 13 times

Bartek

5 years, 9 months ago

No. Becouse Xact_Abort is set to "ON" at the begining and when somethink II go wrong then error information II not skip to Catch block

upvoted 4 times

Bartek

5 years, 9 months ago

XACT_ABORT behaves differently when used in a TRY block. Instead of terminating the transaction as it does in unstructured error handling, XACT_ABORT transfers control to the CATCH block, and as expected, any error is fatal.

upvoted 3 times

...

MarcusJB

5 years, 4 months ago

I'm not sure, if this question is maybe really about the semicolon. In the book to this course exactly this scenario won't work because of the missing semicolon. SQL Server would try to rollback a transaction named "THROW", which obviously doesn't exist. So if being exact the correct answer would really be "No" even if the rest works fine in my eyes.

upvoted 8 times

...

mlourinho

Highly Voted 5 years, 7 months ago

The answer A with the semicolon works fine. IF OBJECT_ID('DISTRICTS') IS NOT NULL DROP TABLE DISTRICTS; CREATE TABLE DISTRICTS( ID_District INT IDENTITY , DistrictName VARCHAR(255) NOT NULL , City VARCHAR(2) ); INSERT INTO DISTRICTS(DistrictName) VALUES('London') -- SELECT * FROM DISTRICTS IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME = 'InsertDistrict') DROP PROCEDURE InsertDistrict GO CREATE PROCEDURE InsertDistrict @DistrictName VARCHAR(255) , @City VARCHAR(255) AS BEGIN SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION INSERT INTO DISTRICTS(DistrictName, City) VALUES( @DistrictName, @City ) COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; THROW 51000, 'O registo is wrong.', 1; END CATCH END GO EXEC InsertDistrict 'London', 'Reading'

upvoted 10 times

mlourinho

5 years, 7 months ago

XACT_STATE Is a scalar function that reports the user transaction state of a current running request. IF (XACT_STATE()) = -1 -- If -1, the transaction is uncommittable and should be rolled back. IF (XACT_STATE()) = 1 -- If 1, the transaction is committable. IF (XACT_STATE()) = 0 -- XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.

upvoted 2 times

...

Billybob0604

Most Recent 4 years, 5 months ago

correct, all requirements would have been met if that semicolon would have been there. Type of mistake plays a big role. compile errors will leave the batch right away. no jumping to catch block. drop table #test create table #test ( custid varchar(5) not null ,income money ) if object_ID('abort_test','P') IS NOT NULL drop proc abort_test go create proc abort_test as begin SET NOCOUNT ON begin try begin tran insert into #test (custid, income) values ('A5666', 20000) insert into #test (custid, income) values ('A56666', 20000) --select roses from garden --select esx , bladiebla from ex dije commit tran end try begin catch print 'rolling back the tran'; if @@trancount > 0 rollback tran; throw 51000, 'Quite a big mistake', 1; end catch; end set xact_abort ON; exec abort_test select * from #test

upvoted 1 times

...

NickNnack

4 years, 5 months ago

This question is basically a duplicate of question 1

upvoted 1 times

geekeek1

4 years, 5 months ago

yes but that's not the answer to the question. so stop adding extra comments which are not needed

upvoted 1 times

...

Andy7622

4 years, 6 months ago

*rollback

upvoted 1 times

...

Andy7622

4 years, 6 months ago

it's not about semicolon . it 's about can we use xact_abort on together with try___catch block. I hear an opinion that exact_abort follback transaction and there is exact_state() that gives zero in catch block so no error mesage is thrown.

upvoted 1 times

...

MOAMMI

4 years, 6 months ago

if xact_state() <> 0 rollback tans, means its 1 which is commitable or -1 uncommitable. in both cases it will roll back which is not the desired output. we need to rollback if it is -1 or commit if it is 1.

upvoted 1 times

...

Oooo

4 years, 9 months ago

I think the possible reason for no is - you are throwing an user defined error message 'Product cannot be created' which is not asked in the question . The question just says throw 5100.

upvoted 1 times

...

vermeilyn

5 years, 1 month ago

You can refer to Querying Data with Transact SQL Exam Ref 70-761 page 305

upvoted 2 times

...

vermeilyn

5 years, 1 month ago

The correct answer is Yes. You can use XACT_ABORT with TRY-CATCH. When XACT_ABORT is on inside TRY-CATCH, you need to check if @@TRANSCOUNT > 0 or XACT_STATE <> 0. Even if you don't ROLL BACK TRANS, the THROW command will.

upvoted 1 times

...

ertanasan

5 years, 2 months ago

Without a semicolon after rollback transaction, that code even doesn't compile.

upvoted 2 times

...

Vanesa30

5 years, 3 months ago

yes, with the semicolon after rollback tranaction work perfectly

upvoted 3 times

Anette

5 years ago

what if it is NO because of that semicolon? In the end it is wrong

upvoted 1 times

...

avramov

5 years, 7 months ago

i think NO, because if there is an error, because xact_abort is on, the tran is already rolled back because of this option ON. there is no need to roll back it again

upvoted 1 times

Jiacheng

5 years, 4 months ago

xact_abort in try_catch block works different, if there is somethign wrong in try block, and we set xact_abort to ON, then we need to rollback manually, if don't use try_catch, it can be rollback automatically

upvoted 3 times

...

Scooter123

5 years, 7 months ago

I think it met the goal like llray said, tested OK by my side, the XACT_STATE() is -1, so there is a rollbackable transaction, the ROLLBACK transaction works fine.

upvoted 3 times

Anette

5 years ago

I tested it too. Even if the IF statement wasn't it will work perfectly.

upvoted 1 times

...