July 24, 2003 at 8:08 am
I have a stored procedure that has XACT_ABORT ON set at the beginning. The stored procedure has two INSERT statements. When I added a record, the second INSERT failed because the primary key column was not set to allow nulls.
I was under the impression that such an error should have rolled back the first INSERT. However it did not.
Isn't any error that occurs in the stored procedure supposed to roll back all transactions created in that stored procedure? Or is it I have to explicitly do a BEGIN TRANS for XACT_ABORT to work the way I assumed? Thanks.
--Lenard
July 24, 2003 at 7:58 pm
Hi there, as per the doco, note the begin trans:
SET XACT_ABORT ON
GO
BEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
INSERT INTO t2 VALUES (6)
COMMIT TRAN
GO
/* Key 5 insert error with XACT_ABORT ON caused
all of the transaction to roll back. */
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 24, 2003 at 8:42 pm
Thanks Chip. I see that I forgot to wrap it around a transaction. For some reason I assumed the SP was itself the transaction wrapper when XACT_ABORT ON was set. 🙂
--Lenard
August 19, 2003 at 5:14 am
You might wan't to turn it off again afterwards though. I believe that its a connection wide property, and will therefore affect other transactions if you are re-using your connection 😉
August 19, 2003 at 5:50 am
We never use this option while using the ADO 2.5 or the latest ADO.net. Does this mean that these object libraries do it on their own.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 19, 2003 at 6:13 am
Sorry for the previous question.
The point that I had mentioned is that we do not use this statement when working with ADO 2.5 and ADO.NET.
What I missed out there was that in those cases the transaction was controlled from the front end and on an error occuring the transaction was rolled back.
What I want to stress is that the transaction is not controlled in the backend.
Anyways it was something new and would help me when I control transaction in the backend.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 20, 2003 at 9:51 am
We have found that transactions work great by using the BEGIN TRAN and COMMIT or ROLLBACK - and NOT using XACT_ABORT. The only time we have ever had to use XACT_ABORT is when the transaction involves multiple SQL machines in a distributed transaction.
I don't know if that is the preferred method - but I haven't had any problems in the last five years with this approach.
Guarddata-
August 20, 2003 at 11:18 am
I wanted a way so that I didn't have to check for errors after each action statement. By using XACT_ABORT, I catch my errors in my .NET web application and not worry about ALSO adding extra code to handle the checks in my stored procedure.
I'm sure many will disagree but my stored procedure code are much more readable due to less clutter. I find it also easier to maintain because I have only one place to catch my errors.
--Lenard
August 20, 2003 at 11:28 am
That is a good start. However - this will only catch errors relating to violations in SQL itself (CHECK and FOREIGN KEY constraints - duplicate indexes, etc.)
If that is what you need to catch, this approach probably works fine. We tend to have issues that are not all controlled by the database and cannot use this as our only means of error trapping.
Guarddata-
August 20, 2003 at 2:57 pm
GuardData, you are correct. As a matter of fact, I do return custom codes to my application because as you stated they won't be handled by the XACT_ABORT statement. For example, in my update stored procedures, I need to check for concurrency errors.....so I return an error code for that. I basically only use XACT_ABORT to prevent action statements down stream from executing should those SQLServer violations occur.
--Lenard
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply