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)

Error handling

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-19 : 07:21:29

Sonali writes "I have an issue with error trapping in sQL server. I am using the following code to attempt to log any failures into the msg table for the stored procedures.

If @@Error<>0
begin
select @MsgDate=getdate()
select @strMsg=substring(description, 1, 150) from master.dbo.sysmessages where error=@@Error
insert into IntLog values (@RunGroup, 'GL', @MsgDate, @strMsg)

end

It doesn't seem to ever work. I don't get any messages in the intlog table even when there are SQL error upon execution of the proc.

Why, any help

Thanks
Sonali Kelkar"

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-19 : 07:28:48

Probably because the @@Error variable changes on each statement?

Try:

DECLARE @Err INT

-- Your SQL Here
SET @Err = @@Error
If @Err<>0
begin
select @MsgDate=getdate()
select @strMsg=substring(description, 1, 150) from master.dbo.sysmessages where error=@Err
insert into IntLog values (@RunGroup, 'GL', @MsgDate, @strMsg)
end

HTH

Sam

Edited by - SamC on 05/19/2003 07:29:26

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-19 : 09:20:42

quote:
Sam: Probably because the @@Error variable changes on each statement?

Yup, that's it.

OS

Nazim
A custom title

1408 Posts

Posted - 2003-05-19 : 10:17:43

http://sqlteam.com/item.asp?ItemID=6881

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-19 : 13:54:26

I think 1 little thing is missing from this. What would you do, with in a transaction if a statement failed? I'd rollback the transactions. And in that case, the insert in to the log must occur after the ROLLBACK.

I was trying to log the steps throughout a load process. Happily writing the reults of each process to a log...except when a step near the end failed. Everything rolled back, and I couldn't tell where the failure occurred.

I fixed that by echo-ing the statements out to a text file on the box, and the doing a bcp of the log after the rollback.

I mention all of this, because with the statement in the Post, there is nothing stopping the insert with a Error value of 0. It has to have been rolled back.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources