Exam 70-762 topic 1 question 131 discussion

Actual exam question from Microsoft's 70-762

Question #: 131
Topic #: 1

You have the following stored procedure:

The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.
What are two possible ways to resolve the issue? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

A. Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.
B. Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.
C. Add a COMMIT TRANSACTION statement after the INSERT statement.
D. Remove the SET ANSI_DEFAULTS ON statement.

Show Suggested Answer

Suggested Answer: CD 🗳️
SET ANSI_DEFAULTS is a server-side setting that the client does not modify. When enabled (ON), this option enables SET IMPLICIT_TRANSACTIONS (and some other options).
The SET IMPLICIT_TRANSACTIONS, when ON, the system is in implicit transaction mode.
This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN
TRANSACTION being executed first: ALTER TABLE, FETCH, REVOKE, BEGIN TRANSACTION, GRANT, SELECT, CREATE, INSERT, TRUNCATE TABLE,
DELETE, OPEN, UPDATE, DROP.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-2017

by lh2607 at Nov. 19, 2020, 10:39 a.m.

Comments

Submit Cancel

lh2607

4 years, 5 months ago

Correct

upvoted 4 times

...

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 131 discussion

Comments

lh2607

SY0-701