log error messages in a table

[dataforums]

Hi,

Can anyone plz. tell me how to print the error number and the error message and insert them into a log table. And also do I need to check for errors after every DML statement or can I just handle them at the end of each block??

Thanks in advance.

 

[NoCoolHandle]

This has probably been covered very well here in the past, however the keys to what you need.

There is a "global variable" @@error which is what you need to test for.

The trick is that just checking it clears it

ie.
If @@error <> 0
begin
insert into sometable values (@@error)
end

would always return 0

so the key is to set a local variable to the global one and then test it..

ie
declare @e int
set @e = @@error
if @e <> 0
begin
insert into sometable values (@e)
end

That gives you the error in your db..

NOW the next step is the text...
Not quite so easy.. Much of the text that shows up in errormessages is specific to the raising of the error... However if you want the definintion of the errormessage that can be found in master.dbo.sysmessages

the trick then is to query the sysmessages table based on the errornumber...

Ie

declare @e int
set @e = @@error
if @e <> 0
begin
declare @m varchar(8000)
select @m = description from master.dbo.sysmessages where error = @e
insert into sometable values (@e,@m )
end


HTH


Rob

 

[dataforums]

Thanks Rob. I'll try it.

 

[dataforums]

This code works but do i have to put this in each section of code?? Is there any other way to put this in a genralized block and call it like when we check the error exist :

"if @e <> 0 then go to the error block"


declare @e int
set @e = @@error
if @e <> 0
begin
declare @m varchar(8000)
select @m = description from master.dbo.sysmessages where error = @e
insert into sometable values (@e,@m )
end

 

[NoCoolHandle]

TSQL does support goto, so yes.. that would work.

The key would be... do you want to abort all processing at that point of continue on..

IF you could support failure and contiune logic you would almost be better to keep each block, but if you want to catch.. Do some reporting and exit running no other statments then a GoTO would be ok..

GOTO syntax

declare @e int
delete from sometable where id = SomeIdThatCouldCauseAForeginKeyerror
set @e = @@error
if @e <> 0
begin
GOTO ErrHand
end
Update sometable set id = 23 -- causing cascade update issues..
set @e = @@error
if @e <> 0
begin
GOTO ErrHand
end



ErrHand:
declare @m varchar(8000)
select @m = description from master.dbo.sysmessages where error = @e
insert into sometable values (@e,@m )
end



Other good keywords are

RETURN -- Stop all processing and exit the proc
BREAK -- get out of the loop but contiue..

 

[NoCoolHandle]

Silly question...

are you folks developing with DotNet?

(it defintily relates to problems you might have with trapping errors and continuing..)