How to get the correct error message

Hi Everyone!

In my stored procedure I check for any errors during .
If there are any errors I log them. (by checking @.@.ERROR)

What my problem is, the error message 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:

* Error ID: 547
* Error Desc: %ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.

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

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.

Thanks heaps,

rochanaFORMATMESSAGE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0kv9.asp),
Using RAISERROR (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_05_3f76.asp) ?|||Thanx for your reply buser

Well.. I could use FORMATMESSAGE if i know the parameter values to pass. Because the system issued the RAISERROR I do not have a way of retrieving (or passing) the parameter values to FORMATMESSAGE

I do not know if there is a way to get parameter values for the error raised by the sql server.

But I think there should be a way, because the Query Analyzer displays the full error message when I execute the stored proc in there.

I hope what I am telling is clear :)

Syntax

FORMATMESSAGE ( msg_number , param_value [ ,...n ] )|||I think not... :( Because who is catching error also should do form the message for a mistake... In this case do rase exception sql server...
You can write any errors (or some) to SQL server log file and then using DTS transfer this to some table...
I ask me to correct if it not so...
Sorry for my english :)
see sysmessages col:=dlevel (0x80) and
sp_altermessage [ @.message_id = ] message_number
, [ @.parameter = ] 'write_to_log'
, [ @.parameter_value = ] 'value'|||Hi Buser

One guy pointed me to this on another forum.

http://www.nigelrivett.net/spFormatOutputBuffer.html

And it seems to give the full error message, but not properly formatted.

Thanks for ur help|||Permissions
DBCC OUTPUTBUFFER permissions default only to members of the sysadmin fixed server role, who can see any SPID. Permissions are not transferable.

select @.cmd = 'dbcc outputbuffer(' + convert(varchar(10),@.spid) + ')'
exec (@.cmd)|||yes. can only view if u have admin rights :(