Error details/description rather than just @@Error number

Using SQL Server 2000

I am trying to get the error description that you get in Query Analyzer or in a web page connection - only directly in a stored procedure in MS SQL.

Basically this message:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'UPKCL_pubind'. Cannot insert duplicate key in object 'publishers'.
The statement has been terminated.

A very detailed example of what I am looking for is here: http:Q_20336836.html
It even shows examples of what is being looked for and what the template in master.dbo.sysmessages looks like.

However, the solution there was to get the information through the web server/programming code - not directly within a stored procedure.

I also looked at the link here:

which was an article recommended here : http:Q_20891906.html as a way to get the output buffer, but it is not clean like the above message and I need the clean message.

It seems strange to me that SQL Query Analyzer can get this error information.
Programming languages have this information passed as a database exception/error message (depending on the language), but that the error cannot be accessed in SQL itself.

Any ideas on how this can be done?
LVL 35
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
The good news is there is a T-SQL function to do this: FORMATMESSAGE

The bad news is that it only "works with error messages between the values of 13000 and 2147483647"
BUG: FORMATMESSAGE Works with the Same User-Defined Messages as RAISERROR;en-us;281680

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
In case you are wondering why I pointed that out:  I thought I would save you the effort of going down that road.
mrichmonAuthor Commented:
so it does't work for errors like constraint violations or insert failures?

The problem I have is that we have a nightly job and if an error occurs I can give a generic message as to where in the stored proc it was, but would like to email the actual error to support so that we can investigate why it failed.  As many of the tasks in the stored proc are time sensitive for hitting other databases - sometimes if we run it later the error changes or is no longer there.  By emailing the actual error to support we can determine if we can avoid the problem in the future by adjusting the time the job runs.

I appreciate you pointing this out.  I am guessing it is related to the fact that user defined error messages do not have the template variables that need to be filled in which is why that would only work with those errors.

Any ideas on how to get the message for standard errors?  Or how Query ANalyzer does it?  Or how it is passed to web languages such as through .NET to C#?
Anthony PerkinsCommented:
>>Any ideas on how to get the message for standard errors?<<
I am afraid not.
Anthony PerkinsCommented:
Strange ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.