You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second
statement.
3. Error information should be returned to the client.
What should you do?
A.
Option A
B.
Option B
C.
Option C
D.
Option D
Explanation:
* SET XACT_ABORTWhen SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the
entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that
raised the error is rolled back and the transaction continues processing.
It’s D. See number 3 in the question. Answer A doesn’t return error information to the client
It’s C. Although the @@ERROR check isn’t needed. Could be
SET XACT_ABORT ON
BEGIN TRANSACTION
Statement 1 (SELECT e.g. 1/0)
Statement 2 (SELECT 5)
COMMIT TRANSACTION
What XACT_ABORT_ON does is replace the code above with this implicitly:
BEGIN TRANSACTION
BEGIN TRY
Statement 1
Statement 2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
THROW
END CATCH
should be A? app could check error by access @@error
B: does not rollback
c: will execute 2 even if 1 fails
d: there is no goto in try catch
‘C’ would not execute the 2nd even if the 1st statement fails because of the “set xact_Abort on”
It would be so if “set xact_Abort off”
“if @@error 0” would only check the @@error value after the 2nd statement but it is not necessary but it works
the following would be enough:
set xact_Abort on
begin transaction
.. 1st Statement
.. 2nd Statement
commit transaction
spot on
C.
A. Error information not returnes to the client.
B. Sintax error: TRY…CATCH
C. CORRECT ANSWER
D. Sintax error: GOTO
It is C…
set xact_abort on
begin tran
print 1/0
print ‘Test’
commit tran
if @@error0
rollback
else
commit tran