Which four Transact-SQL segments should you use to develop the solution?

Posted by: Pdfprep Category: 70-762 Tags: , ,

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.

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 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

Leave a Reply

Your email address will not be published.