Transactions & Error handling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transactions & Error handling

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-22 : 18:15:18

I have a store procedure that calls other procedures.
I placed a SET XACT_ABORT ON in the main SP and the IF @@ERROR <> 0 after each SQL Statement, the error handler is something like

ErrorHandeler:
    if @transName<>''
    begin
        ROLLBACK TRANSACTION
        insert into tbleeerror (transname, transDate, transError)
        select @transname, getdate(), @@error 
    end

KeepGoing:
    SET @i = @i + 1

When an error is found I want the sp to rollback the transaction, add a record to a error log table and keep going but it just stops when the error if found

A candle loses nothing by lighting another candle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-22 : 18:29:26

That's the problem with SET_XACT_ABORT I believe. It aborts the entire stored procedure on error.

Check this out:
http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart2.asp

Tara

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-25 : 14:46:47

Many thanks

I change it to SET XACT_ABORT Off and it did not stop on the first error.

But I still have to work on it I saw there were many errors but no new records on the error table

A candle loses nothing by lighting another candle

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-26 : 22:50:04

What level error is this?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-27 : 15:29:37

Well I'm currently working in several projects at the same time and I was ask to leave this one behind for a while.

I'm not sure what you are asking by error level but most of the errors say level 14 and some others 16. None of the errors (I think) were generated by the main SP instead they occured in the others SPs (SP1, SP2, SP3)

Main
|
===SP1
|
===SP2
   |
   ===SP3

I'm thinking of merging al the SPs in just one

A candle loses nothing by lighting another candle

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-27 : 18:18:34

If you're getting level 16 or higher, you need to figure out what's causing those. There is no way to really handle those gracefully. They usually point to design, coding, or system errors. Post some examples so we can see them.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-04-28 : 08:37:33

Server: Msg 2627, Level 14, State 1, Procedure spEE_Contact, Line 20
Violation of PRIMARY KEY constraint 'PK_tblSubscPersonal'. Cannot insert duplicate key in object 'tblSubscPersonal'.
Server: Msg 2627, Level 14, State 1, Procedure spEE_Contract, Line 75
Violation of PRIMARY KEY constraint 'PK_tblSubscInfo'. Cannot insert duplicate key in object 'tblSubscInfo'.
Server: Msg 547, Level 16, State 1, Procedure spEE_Life, Line 28
INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_tblSubscInfo_tblIndivInfo'. The conflict occurred in database 'Dbxxx', table 'tblSubscInfo'.
Server: Msg 2627, Level 14, State 1, Procedure spEE_Contract, Line 75
Violation of PRIMARY KEY constraint 'PK_tblSubscInfo'. Cannot insert duplicate key in object 'tblSubscInfo'.
Server: Msg 547, Level 16, State 1, Procedure spEE_Life, Line 28
INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_tblSubscInfo_tblIndivInfo'. The conflict occurred in database 'Dbxxx', table 'tblSubscInfo'.
Server: Msg 2627, Level 14, State 1, Procedure spEE_Contract, Line 75
Violation of PRIMARY KEY constraint 'PK_tblSubscInfo'. Cannot insert duplicate key in object 'tblSubscInfo'.

I think these errors are due to incomplete transactions in prior testings. I have to make some queries to prove that though. If my asumtion is true then these errors shouldn't occur once I delete incomplete transactions or all of them since this DB is not being used yet by anyone else. What I'm really concern is why I did not get any records inserted in the error log table...Shouldn't the errors in the SPs called by the main SP cause an error in main SP and thus a record be inserted in the error log table?

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources