October 15, 2019 at 9:08 pm
By forcibily i tried to fail insert statement (Insert into dbo.student) by entering 1/0 value into datetime column. when i ran the process. it didn't inserted any records into dbo.student table but count logged in to log table . it didn't went to catch block and rollback the transaction.
How to explicitly rollback it? that goes to catch block?
October 15, 2019 at 9:48 pm
Certain errors by default won't fail the entire transaction/batch. Before the transaction, use:
SET XACT_ABORT ON
to make SQL fail the entire transaction if an error like that occurs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2019 at 9:57 pm
You've been around long enough to know that you should use the Insert/edit code sample command.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 15, 2019 at 10:01 pm
Certain errors by default won't fail the entire transaction/batch. Before the transaction, use:
SET XACT_ABORT ON
to make SQL fail the entire transaction if an error like that occurs.
You should get into the habit of using semicolons (;) for statement terminators. Use the following instead.
SET XACT_ABORT ON;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 16, 2019 at 12:30 am
Because of this my main table is locking; i have to rollback explicitly if it fails.
for this SET XACT_ABORT ON; will work?
October 16, 2019 at 4:07 am
EXEC dbo.LogTable @Inserted,@Updated is outside of the catch block, so it will run after the rollback.
Also, @Inserted and @Updated are variables, which do not revert their value on rollback. You would need to explicitly set them to 0 in the catch block.
October 16, 2019 at 8:05 am
SET XACT_ABORT ON; will always help but It is advisable to have Rollback with specific Transaction name in respective Catch block.
October 16, 2019 at 2:03 pm
Is there a business definition that defines an "error" or is the error table only supposed to contain errors generated by sql? Please post the definition of the student table (which ought to contain an integer identity primary key). Also, Scott, Drew and the others made important suggestions please incorporate.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply