How to get the error code - 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)

How to get the error code

Author

Topic

jek
Starting Member

9 Posts

Posted - 2004-01-29 : 18:34:22

Hi Everyone!

I have a stored procedure & in that I check for any errors occured during its execution.
If there are any errors I log them. (by checking @@ERROR) These errors are generated by the sql server(not user defined errors)

What my problem is, the error messages that's been logged contain place holders like %s, %l, %d,etc. along with the error message.

How can I get the full error message, with place holders replaced by real error values/text?

Here is a sample what I get as the error (e.g for @@ERROR=547)
* Error ID: 547
* Error Desc: %ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.

I obtain it using this query


SELECT @errdesc=description FROM master.dbo.sysmessages WHERE error = @errid

(Try it your self and see with @errid as 547. You will know what I am talking about)

When I run the stored proc in Query Analyser it gives the actual error message as:

Server: Msg 547, Level 16, State 1, Procedure sp_register_change, Line 366
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_TBL_EQUIPMENT'. The conflict occurred in database 'EquipManWT', table 'TBL_EQUIPMENT', column 'unitno'.
The statement has been terminated.

The Query analyser has substituted values 'INSERT,COLUMN FOREIGN KEY, FK_TBL_EQUIPMENT' etc. when displaying.

(I know that FORMATMESSAGE can be used to show the error.) But as the parameter values for FORMATMESSAGE is unknown to me I cant use it.
msg_number is known as it is the actual @@ERROR


FORMATMESSAGE ( msg_number , param_value [ ,...n ] )

I hope my question is clear

I have checked in several forums but could not find anything related.

Thanks heaps,

rochana

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-29 : 19:17:54

Check Nigel's info here: http://www.nigelrivett.net/spFormatOutputBuffer.html

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

jek
Starting Member

9 Posts

Posted - 2004-01-29 : 19:41:24

Thanks I will check that

jek
Starting Member

9 Posts

Posted - 2004-01-29 : 20:18:24

It does return the full error message. :) Though the error message in the buffer is badly formatted.

Thanks a lot Mark.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources