log error messages in a table - 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

Transact-SQL (2000)

log error messages in a table

Author

Topic

dataforums
Starting Member

14 Posts

Posted - 2005-05-18 : 13:31:06

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.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-18 : 15:53:14

select * from master..sysmessages where error = yourErrorNumber
will give you the messages.

to get an error you need to do
select @@error
after each row.

more details would be nice.
how do you define a block? a transaction? just a piece of code?

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-18 : 17:05:58

>> do I need to check for errors after every DML statement
yes

>> how to print the error number and the error message and insert them into a log table.
A lot of errors will abort the connection and so can only be logged from a client.
The data in sysmessages has placeholders for parameters (table/index names etc.)
To get the error message use
http://www.mindsdoor.net/SQLTsql/spFormatOutputBuffer.html
But it's probably not worth it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dataforums
Starting Member

14 Posts

Posted - 2005-05-19 : 09:47:17

This code works wheni put it after each sql query, 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

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-19 : 12:59:56

You can place after each statement
select @error = @@error, @rowcount = @@rowcount
if @error <> 0 goto errhandler

But it has to be the very next statement otherwise @@error will be lost.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources