Capturing @@Error Message

Question

Post reply

Capturing @@Error Message

AVB

SSChampion

Points: 11418
More actions
February 10, 2010 at 1:59 pm

#210720

I'm not sure if this is possible in SQL 2000 but I'm trying to store the Error Message in a local variable so I can output it or include it in an email. If for example I get a check constraint violation the error in EM or Query Analyzer would be displayed like so:
Msg 547, Level 16, State 0, Procedure sTest, Line 70
UPDATE statement conflicted with COLUMN CHECK constraint 'CK_tTest. The conflict occurred in database 'DB1', table 'tTable, column ID.
So I'd like to capture the whole message if possible. I know that @@ERROR returns the number but that doesn't give me the specifics. Is this possible?
Create Proc s_Test
as
decare @Error int
Insert Into tTable (ID)
select 1 as ID
SET @Error = @@Error
IF @Error <> 0
BEGIN
GOTO Error
END
Error:
SET @Body = @@Error Message goes here
exec CDOSend....

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Using BOL for 2000 look at this:

sysmessages

Contains one row for each system error or warning that can be returned by Microsoft® SQL Server™. SQL Server displays the error description on the user's screen.

For example:

SELECT Severity,description FROM master..sysmessages WHERE error = 5808

OR:

DECLARE @em VARCHAR(255)

SET @em = (SELECT description FROM master..sysmessages WHERE error = 5808)

SELECT @em

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

AVB SSChampion Points: 11418 More actions · Answer 2

bitbucket,

Thanks for the reply. I saw something about the sysmessages but I thought those were universal error messages and not the custom error messages that I was seeing. I'll give that a go and post back when I'm done.

WayneS SSC Guru Points: 95460 More actions · Answer 3

The only problem with this is that you do not get the actual error message... you will get the variable holders in the string in sysmessages, instead of the actual string with the variables substituted. i.e. "foreign key constraint violation: foreign key %s in table %s references table %s"

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

AVB SSChampion Points: 11418 More actions · Answer 4

hmmmm... is there a way to replace those placeholders to obtain the table name or the constraint that was violated?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

and not the custom error messages that I was seeing.

Refer to WayneS post, the "custom" message you receive is because the place holders described in WayneS post are replaced with the actual procedure text.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

There are two ways to replace the place holders ... read the following subjects in BOL

RAISERROR and/or FORMATMESSAGE.

It will not be easy and may require a great deal of code, since the sequence and number of parameters needed to be passed depends on the particular error message.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

AVB SSChampion Points: 11418 More actions · Answer 7

Thanks for the replies.

I'm not using RaisError so it's all about sysmessages and placeholders for me. I've searched around and one article suggested logging those errors and reading the error log. I've quickly read about FormatMessage too. It's a pain regardless. At least I can now see what the error message is and I have detailed error handling when it errors to know what DML statment caused the problem so my problem is at least partially solved.

Thanks again. I appreciate the help!