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:
Answer: 
Explanation:
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 TRANSACTIONGRANT
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.https://technet.microsoft.com/en-us/library/ms187807(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms189797(v=sql.110).aspx