Note: This question is part of a series of questions that present the same scenario. Each question in
the series contains a unique solution that might meet the stated goals. Some question sets might have
more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these
questions will not appear in the review screen.
You create a table named Products by running the following Transact-SQL statement:
You have the following stored procedure:
You need to modify the stored procedure to meet the following new requirements:
Insert product records as a single unit of work.
Return error number 51000 when a product fails to insert into the database.
If a product record insert operation fails, the product information must not be permanently written to the
database.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A.
Yes
B.
No
Explanation:
With X_ABORT ON the INSERT INTO statement and the transaction will be rolled back when an error is
raised, it would then not be possible to ROLLBACK it again in the IF XACT_STATE() <> O ROLLACK
TRANSACTION statement.
Note: A transaction is correctly defined for the INSERT INTO ..VALUES statement, and if there is an error in
the transaction it will be caughtant he transaction will be rolled back, finally an error 51000 will be raised.
Note: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire
transaction is terminated and rolled back.
XACT_STATE is a scalar function thatreports the user transaction state of a current running request.
XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is
capable of being committed.
The states of XACT_STATE are:
0 There is no active user transaction for the current request.
1 The current request has an active user transaction. The request can perform any actions, including writing
data and committing the transaction.
2 The current request has an active user transaction, but an error hasoccurred that has caused the
transaction to be classified as an uncommittable transaction.https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx
Why?
0
0
If a product record insert operation fails, the product information must not be permanently written to the database. Meaning for both XACT_STATE() =1 and XACT_STATE() = -1
Hence If XACT_STATE() 0.
What do you think?
0
0
Answer B is correct.
Using XACT_ABORT ON will work as described in the explanation. XACT_STATE () will return 0 as the rollback is already performed.
See sample code below from Microsoft documentation slightly enhanced:
IF OBJECT_ID(N’t2′, N’U’) IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N’t1′, N’U’) IS NOT NULL
DROP TABLE t1;
GO
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);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); — Foreign key error.
INSERT INTO t2 VALUES (3);
SELECT 100 + XACT_STATE();
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); — Foreign key error.
INSERT INTO t2 VALUES (6);
SELECT 200 + XACT_STATE();
COMMIT TRANSACTION;
GO
SELECT 300 + XACT_STATE();
— SELECT shows only keys 1 and 3 added.
— Key 2 insert failed and was rolled back, but
— XACT_ABORT was OFF and rest of transaction
— succeeded.
— Key 5 insert error with XACT_ABORT ON caused
— all of the second transaction to roll back.
SELECT *
FROM t2;
GO
0
1
The question wants us : ” Return error number 51000 ” not Raising Error.
Code is correct but because stored procedure dose not return number the Answer is B.
Watch Pluralsight-SQL Server 2012: Transact-SQL Error Handling . it is helpful.
0
1
My explanation is wrong.
I run the above question on SQL Server and Result is :
“Incorrect syntax near ‘50001’.”
The code has a textual problem.
Because after “Rollback Transaction” there is not semicolon , It works like this :
ROLLBACK TRANSACTION savepoint_name.
However, the option B is correct but explanation of answer is wrong.
12
1
I approve
1
1
the correct answer is no.
ES mas por un tema de sintaxis en el bloque CATCH.
AQUI VA UN EJEMPLO DE MICROSOFT LIGERAMENTE MODIFICADO:
CREATE TABLE test.Productos
( ProductID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ProductName NVARCHAR (50)NULL CONSTRAINT Check_EmptyString CHECK (ProductName”)
);
INSERT INTO test.Productos VALUES(‘MouseRadioshackModel271681’)
–para verificar la existencia de la restriccion el mensaje de error
INSERT INTO test.Productos VALUES(”)
CREATE PROCEDURE test.InsertProductos
@ProductName NVARCHAR (50)
AS
–HASTA AQUI OK
BEGIN
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO test.Productos(productname)
VALUES(@ProductName );
— Instrucción INSERT en conflicto con la restricción CHECK “Check_EmptyString”.
PRINT ‘OK’
COMMIT TRANSACTION
END TRY
BEGIN CATCH
–PRINT ‘In catch block.’;
IF XACT_STATE()0
THROW 51000,’Message Cstom!!!!!’,1
ROLLBACK TRANSACTION
END CATCH;
END
EXEC test.InsertProductos @ProductName=’PANINIAlbumRussia2018-xfs893′
EXEC test.InsertProductos @ProductName=”
select * from test.Productos
3
0
buena compatriota!
0
0
CREATE PROCEDURE test.InsertProductos
@ProductName NVARCHAR (50)
AS
–HASTA AQUI OK
BEGIN
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO test.Productos(productname)
VALUES(@ProductName );
— Instrucción INSERT en conflicto con la restricción CHECK “Check_EmptyString”.
PRINT ‘OK’
COMMIT TRANSACTION
END TRY
BEGIN CATCH
–PRINT ‘In catch block.’;
IF XACT_STATE()0
THROW 51000,’Message Cstom!!!!!’,1
ROLLBACK TRANSACTION
END CATCH;
END
EXEC test.InsertProductos @ProductName=’PANINIAlbumRussia2018-xfs893′
EXEC test.InsertProductos @ProductName=”
select * from test.Productos
0
0
The ‘rollback transaction’ is missing a semicolon.
I think the the procedure does meet its goals, although it is not the best t-sql.
5
0
Correct Answer is B)
WHY?
1) Rollback transaction does not have a semicolon. and the Throw statement requires a semicolon before it. Just like the WITH of a CTE.
2) If the semicolon was there the code would work.
3) When XACT_ABORT ON and error occurs in a try, it activates the catch and the XACT_STATE() is -1 (uncommitable).
4) When XACT_ABORT ON and error occurs in a try, it activates the catch and the transaction is automatically rolled back if you don’t explicitly rollback.
5) Test by yourself:
[based on eder’s reply]
DROP TABLE IF EXISTS Productos
CREATE TABLE Productos
(
ProductID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ProductName NVARCHAR (50)NULL CONSTRAINT Check_EmptyString CHECK (ProductName ”)
);
INSERT INTO Productos VALUES(‘MouseRadioshackModel271681’)
/*TEST CHECK CONSTRAINT*/
INSERT INTO Productos VALUES(”)
CREATE OR ALTER PROCEDURE InsertProductos
@ProductName NVARCHAR (50)
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Productos VALUES(‘001’)
INSERT INTO Productos(productname)
VALUES(@ProductName );
PRINT ‘OK’
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ‘In catch block.’;
IF XACT_STATE() 0
PRINT ‘AUTO ROLLBACK BECAUSE OF XACT_ABORT ON’; /*ROLLBACK TRANSACTION;*/
THROW 51000,’Message Cstom!!!!!’, 1
END CATCH;
SELECT * FROM Productos
END
EXEC InsertProductos @ProductName=’PANINIAlbumRussia2018-xfs893′
EXEC InsertProductos @ProductName =”
select * from Productos
4
0
The answer is B because;
1. XABORT-ON “specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.”
2. The code states ” IF XACT_STATE()0, which means XACT_STATE = 1 or -1 ?
a. IF XACT_STATE: — If 1, the transaction is committable; and
b. If XACT_STATE = -1, the transaction is uncommittable and should be ROLLED BACK;
c. And, “IF XACT_STATE = 0” means that there is no transaction and a commit or rollback operation would generate an error. (This is not relevant)
Conclusion: The Transaction in the TRY block has already been automatically ROLLED BACK with XACT_ABORT ON.
Therefore, it cannot be ROLLED BACK once again in the CATCH BLOCK with “IF XACT_STATE() 0″.
This is the principle that the question is testing ?
Therefore, I think the ‘semi-colon missing after the ROLLBACK TRANSACTION” could just be a typo error ?
4. .
0
0