ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 16 discussion

Actual exam question from Microsoft's 70-762
Question #: 16
Topic #: 1
[All 70-762 Questions]

DRAG DROP -
You have a database that contains three encrypted store procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT,
UPDATE, DELETE and BACKUP DATABASE statements.
You have the following requirements:
- You must run all the stored procedures within the same transaction.
- You must automatically start a transaction when stored procedures include DML statements.
- You must not automatically start a transaction when stored procedures include DDL statements.
You need to run all three stored procedures.
Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange then in the correct order.
Select and Place:

Show Suggested Answer Hide Answer
Suggested Answer:
Note:
Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, allindividual statements are committed if they complete successfully.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
ALTER TABLE (DDL)

FETCH -

REVOKE -

BEGIN TRANSACTION -

GRANT -

SELECT -
CREATE (DDL)

INSERT -

TRUNCATE TABLE -
DELETE (DML)

OPEN -
UPDATE (DML)
DROP (DDL)
Note 2: XACT_STATE returns the following values.
1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction. The transaction is committable.
-1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.the transaction is uncommittable and should be rolled back.
0 There is no active user transaction for the current request. Acommit or rollback operation would generate an error.
References:
https://technet.microsoft.com/en-us/library/ms187807(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms189797(v=sql.110).aspx
by itdoesntmatter at Feb. 11, 2020, 5:36 p.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
itdoesntmatter
Highly Voted 5 years, 2 months ago
SET IMPLICIT_TRANSACTOPNS ON BEGIN TRY EXEC dbo.Proc1 EXEC dbo.Proc2 EXEC dbo.Proc3 IF (XACT_STATE() = 1) COMMIT TRANSACTION: END TRY BEGIN CATCH IF (XACT_STATE() != 0) ROLLBBCK TRANSACTION END CATCH SET IMPLICIT_TRANSACTOPNS OFF
upvoted 51 times
JohnFan
5 years, 2 months ago
There are three possible values: 1-There is an active transaction that can be committed; 0-There is no active transaction; -1-There is an active transaction that cannot be committed, also referred to as an uncommitable transaction, or a doomed transaction. An uncommitable transaction is caused by a few rare situations that can occur in complex code such as using XACT_ABORT with an error handler.
upvoted 2 times
...
...
julie2020
Highly Voted 4 years, 8 months ago
In real exam they will ask three out of five. 1.SET IMPLICIT_TRANSACTOPNS ON Begin trans 2.BEGIN TRY EXEC dbo.Proc1 EXEC dbo.Proc2 EXEC dbo.Proc3 IF (XACT_STATE() = 1) COMMIT TRANSACTION: END TRY 3.BEGIN CATCH IF (XACT_STATE() != 0) ROLLBBCK TRANSACTION END CATCH
upvoted 5 times
kiri2020
4 years, 6 months ago
if I execute the two statements you put under #1 it opens TWO transactions. Try: select @@trancount as trancount1 SET IMPLICIT_TRANSACTIONS ON begin tran select @@trancount as trancount2
upvoted 2 times
...
julie2020
4 years, 8 months ago
You will not have this two options in the real exam. I gave exam today. COMMIT TRANSACTION SET IMPLICIT_TRANSACTOPNS OFF
upvoted 4 times
blues198
4 years, 7 months ago
Julie can you explain better this options? The correct answer is SET IMPLICIT_TRANSACTOPNS ON Begin trans 2.BEGIN TRY EXEC dbo.Proc1 EXEC dbo.Proc2 EXEC dbo.Proc3 IF (XACT_STATE() = 1) COMMIT TRANSACTION: END TRY 3.BEGIN CATCH IF (XACT_STATE() != 0) ROLLBBCK TRANSACTION END CATCH COMMIT TRANSACTION SET IMPLICIT_TRANSACTOPNS OFF ?
upvoted 1 times
Tinashe16
4 years, 4 months ago
Incorrect. You used 5 blocks. Only 4 required.
upvoted 1 times
...
...
...
...
UsefJuan
Most Recent 4 years, 6 months ago
SET IMPLICIT_TRANSACTOPNS OFF BEGIN TRY EXEC dbo.Proc1 EXEC dbo.Proc2 EXEC dbo.Proc3 IF (XACT_STATE() = 1) COMMIT TRANSACTION: END TRY BEGIN CATCH IF (XACT_STATE() != 0) ROLLBBCK TRANSACTION END CATCH just tested it on SQL server 2016, when you set it to on you end up with 2 transactions and the @@trancount is 1. so you need another commit statement to there to decrease the count to 0.
upvoted 1 times
...
kiri2020
4 years, 6 months ago
these two conditions cannot coexist - - You must automatically start a transaction when stored procedures include DML statements. - You must not automatically start a transaction when stored procedures include DDL statements. Either IMPLICIT_TRANSACTIONS is ON or OFF it treats DDL and DML equally. When IMPLICIT_TRANSACTIONS is ON it starts a transaction automatically if there was no started transaction, which you have to close later manually (commit or rollback) and so you end up with 2 transaction if you run SET IMPLICIT_TRANSACTIONS on and then Begin tran. How this could be a correct answer? Although the question itself is asking for impossible thing, the author of a question doesn't know what they are talking about, they need to read Microsoft docs - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver15
upvoted 2 times
...
melvin9900
4 years, 9 months ago
SET IMPLICIT_TRANSACTOPNS ON ————- BEGIN TRY EXEC dbo.Proc1 EXEC dbo.Proc2 EXEC dbo.Proc3 IF (XACT_STATE() = 1) COMMIT TRANSACTION: END TRY ————- BEGIN CATCH IF (XACT_STATE() != 0) ROLLBBCK TRANSACTION END CATCH ————- SET IMPLICIT_TRANSACTOPNS OFF
upvoted 3 times
...
Jay2
4 years, 11 months ago
There is no right answer.
upvoted 2 times
...
lauferr
4 years, 12 months ago
Why do we need BEGIN TRAN if we set IMPLICIT TRANSACTIONS ON ?
upvoted 4 times
lh2607
4 years, 5 months ago
You don't, the answer given is incorrect. See itdoesntmatters comment above for the correct answer.
upvoted 1 times
...
...
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