Error handling Problem- What am I doing wrong ?

Question

Error handling Problem- What am I doing wrong ?

homebrew01

SSC Guru

Points: 55548
More actions
February 13, 2007 at 2:01 pm

#118933

I'm trying to check for errors, and write to a status file if a statement bombs. Here's a simplified version of my code. It never gets past the error to write to the STATUS table. Thanks for any feedback

DECLARE
@ReturnCode INT
SELECT
@ReturnCode = 0
BEGIN
TRANSACTION
insert
into pubs.dbo.NoSuchTable select * from pubs.dbo.authors
IF
(@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT
TRANSACTION
insert
into DBAdmin.dbo.Status (Refresh_Status, Refresh_Date, Error_Number, Refresh_Code)
select 'Refresh Completed Normally', getdate(), @@ERROR, 'C'
GOTO
EndSave
QuitWithRollback
:
/* IF (@@TRANCOUNT > 0) */ ROLLBACK TRANSACTION
BEGIN
TRANSACTION
insert
into DBAdmin.dbo.Status (Refresh_Status, Refresh_Date, Error_Number, Refresh_Code)
select 'ERROR - Refresh Failed !', getdate(), @@ERROR, 'F'
COMMIT
TRANSACTION
EndSave
:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 1

You need to save the error value. As soon as another statement executes, the @@error value is reset (to the error code . Try this (changed code in red):

 DECLARE @ReturnCode INT
DECLARE @ErrCode int
SELECT @ReturnCode = 0
BEGIN TRANSACTIONinsert into pubs.dbo.NoSuchTable select * from pubs.dbo.authors 
SET @ErrCode = @@ERROR
IF (@ErrCode <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONinsert into DBAdmin.dbo.Status (Refresh_Status, Refresh_Date, Error_Number, Refresh_Code) 
select 'Refresh Completed Normally', getdate(), @ErrCode, 'C'
GOTO EndSaveQuitWithRollback:
/* IF (@@TRANCOUNT > 0) */ ROLLBACK TRANSACTIONBEGIN TRANSACTION
insert into DBAdmin.dbo.Status (Refresh_Status, Refresh_Date, Error_Number, Refresh_Code) 
select 'ERROR - Refresh Failed !', getdate(), @ErrCode, 'F'
COMMIT TRANSACTIONEndSave:

Hope this helps

Mark

Lowell SSC Guru Points: 323518 More actions · Answer 2

Also you've got to watch for the problem where if an error is level 16 or above, execution stops PERIOD... so it would never go to the log stuff.

so if there was a problem with an isnert, PK, foreign key, it would just rollback.

as it sits in SQL 2000, this kind of error checking is only good if you raise your own low importance errors due to business rules....like

'raising error level 10--informational only; great for 'business rules'

if NOT EXISTS(SELECT APPROVAL FROM ACCOUNTING WHERE USERID='MARY PAT' and ORDERID = 55)

RAISERROR (Mary Pat in accounting must approve the invoice prior to completion.', 10, 1)

but those business rules could probably be fixed with check constraints.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!