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