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_ABORT
When 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.
B is the right answer
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
drop procedure test_xact_on
go
create procedure test_xact_on
as
set xact_abort on
begin try
begin transaction
INSERT INTO t2 VALUES (1);
print ‘first SQL’
INSERT INTO t2 VALUES (2); — Foreign key error.
print ‘second SQL’
INSERT INTO t2 VALUES (3);
commit transaction
end try
begin catch
print ‘roll back’
rollback transaction
end catch
go
exec test_xact_on
drop procedure test_xact_off
go
create procedure test_xact_off
as
set xact_abort off
begin try
begin transaction
INSERT INTO t2 VALUES (1);
print ‘first SQL’
INSERT INTO t2 VALUES (2); — Foreign key error.
print ‘Second SQL’
INSERT INTO t2 VALUES (3);
commit transaction
end try
begin catch
print ‘roll back’
rollback transaction
end catch
go
exec test_xact_off
drop procedure test_xact_off_throw
go
create procedure test_xact_off_throw
as
set xact_abort off
begin try
begin transaction
INSERT INTO t2 VALUES (1);
print ‘first SQL’
INSERT INTO t2 VALUES (2); — Foreign key error.
print ‘Second SQL’
INSERT INTO t2 VALUES (3);
commit transaction
end try
begin catch
throw
end catch
go
exec test_xact_off_throw
Answer B incorrect due to sintax error:
BEGIN TRY
..
END TRY
BEGIN TRY
..
END TRY
BEGIN CATCH
…
END CATCH
After every END TRY must be BEGIN CATCH
A is the right answer , your abort off codes are different than the one in the question
Sorry Answer is C cause B and D are wrong and between C and A , C is the only one that provide error information . i ve tried it on a lab
You’ve tried ‘what’ in the lab?! If you did, where is the result of your lab demo?!
With option C, these two requirements aren’t met:
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
{…Statement 1 runs to completion or, is aborted due to an error. Then, processing switches to …Statement 2}. After the two statements complete, a check is made for @@ERROR 0
3. Error information should be returned to the client. This is not achieved with option C.
Option D, is obviously wrong. So, only B seems near-logical option.
Yes the correct Answer is C.
Answer A: Wrong — Because it does not throw error msg back to user.
Answer B: Wrong — Each Begin Try should have its Catch Block. There is One Catch Block missing
What about option C? It does not meet the requirement: “Error information should be returned to the client.”
Option B seems most viable option.
Hello,
No, answer B is not most viable.
Because “A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.” Source: https://msdn.microsoft.com/en-us/library/ms175976.aspx (TRY…CATCH (Transact-SQL))
a+,=)
-=Finiderire=-
Answer D: Wrong – There is label CATCH present
So Answer is C
Correction
Answer D: Wrong – Because there is no label ‘CATCH’ Present.
Answer is A :
C is wrong, B and D with error
Hello,
A.
I agree, even if the third requirement is not clearly met.
/*A*/
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
IF EXISTS (SELECT 1) RAISERROR (‘Error msg because ”SELECT 1” result exists’,16,1);
SELECT ‘I am the Statement 2’ UNION SELECT ‘And I return 2 rows’;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
/*Command(s) completed successfully.
B
*/
SET XACT_ABORT OFF
BEGIN TRY
IF EXISTS (SELECT 1) RAISERROR (‘Error msg because ”SELECT 1” result exists’,16,1);
END TRY
BEGIN TRY
SELECT ‘I am the Statement 2’ UNION SELECT ‘And I return 2 rows’;
END TRY
BEGIN CATCH
THROW
END CATCH
/*
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ‘BEGIN’.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near ‘CATCH’.
C*/
SET XACT_ABORT ON
BEGIN TRANSACTION
IF EXISTS (SELECT 1) RAISERROR (‘Error msg because ”SELECT 1” result exists’,16,1);
SELECT ‘I am the Statement 2’ UNION SELECT ‘And I return 2 rows’;
IF @@ERROR 0
ROLLBACK
ELSE
COMMIT TRANSACTION
/*
Msg 50000, Level 16, State 1, Line 35
Error msg because ‘SELECT 1’ result exists
(2 row(s) affected)
*/
SET XACT_ABORT ON
BEGIN TRY
IF EXISTS (SELECT 1) RAISERROR (‘Error msg because ”SELECT 1” result exists’,16,1);
IF @@ERROR 0
GOTO CATCH
SELECT ‘I am the Statement 2’ UNION SELECT ‘And I return 2 rows’;
IF @@ERROR 0
GOTO CATCH
END TRY
BEGIN CATCH
THROW
END CATCH
/*
Msg 133, Level 15, State 1, Line 58
A GOTO statement references the label ‘CATCH’ but the label has not been declared.
Msg 133, Level 15, State 1, Line 58
A GOTO statement references the label ‘CATCH’ but the label has not been declared.
*/
a+,=)
-=Clement=-
You did not actually start a transaction (begin tran statement is not enough).
Thy replacing your first statement to update that will fail and you’ll fell all the difference.