Error Message retrieval - 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 Message retrieval

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-10 : 16:16:50

I have a stored procedure where an INSERT must be failing occasionally.

I'd like to retrieve the error (@@ERROR is easy), but is there a description that is retrievable that will state error in English? I'd like to post what information I can about the failure in an error table.

Sam

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-02-10 : 17:56:48

Have a look at the sysmessages table in the Master Database in Enterprise Manager. It is a system table that might have the info you need.

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-10 : 20:48:37

Can the message text be retrieved from a user with role dbo? I've found samples on how to store custom errors, but not on how to retrieve custom or permenant error message text.

An example starting from

set @err = @@ERROR
IF @err > 0 BEGIN
DECLARE @RetrievedErrMsg varchar (1000)
...
Print @RetrievedErrMsg

END

Would be great.

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-10 : 20:52:21

Nigel has some code here:

http://www.nigelrivett.com/

That retrieves the full error message from the buffer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-10 : 20:59:08

quote:

Can the message text be retrieved from a user with role dbo?

Yes a user can select from sysmessages and the user/role doesn't even have to be dbo. I added a user to my local machine and didn't grant it anything. I just created it and only input a password. I then logged in as this user and was able to do select * from sysmessages.

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-02-10 : 22:08:00

SamC,

select @RetrievedErrMsg = description from master.dbo.sysmessages where error = @err

However, this won't do the parameter substituion for you. I would be inclined to study nr's method depending on how important your application is

Edited by - darinh on 02/10/2003 22:19:05

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-10 : 22:49:45

I hate to say this but I can't find Nigel's solution on his website.

Am I missing it or is it gone?

Sam

Edited by - SamC on 02/11/2003 01:08:10

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-13 : 05:48:54

It's spFormatOutputBuffer

This gets the error message from the output buffer.
For v2000 I think you have to change @charoffset to 62 and maybe @HexOffset to 11. Try it and see.
It isn't ideal as the start of hte output is always overwritten but for logging to give people something to look at it's ok.

==========================================
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.

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-13 : 07:21:48

Thanks Nigel,

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources