SQL Server error messages

[bobby1977]

Hello,

i'm working with T-SQL stored procedures and i am interested in obtaining the formated error message from sysmessages when a constraint violation occurs. i can obtain the error message using a select from sysmessages table based on the number of error i received through @@ERROR, but the string is not formated and looks like "Cannot insert NULL in table %.*ls, column %.*ls". Is there a method to get the complete string ? Thankx in advance!

 

[Bernadette]

Hi There Bobby

Have you tried using the FORMATMESSAGE function ?
You can use this function to display the message with the parameters replaced by actual values.

Here is an example taken from BOL ...

Example:
=========
This example uses a hypothetical message 50001, stored in sysmessages as “The number of rows in %s is %1d.”
FORMATMESSAGE substitutes the values Table1 and 5 for the parameter placeholders. The resulting string,
“The number of rows in Table1 is 5.” is stored in the local variable @var1.


DECLARE @var1 VARCHAR(100)
SELECT @var1 = FORMATMESSAGE(50001, 'Table1', 5)

Hope this Helps :->



Bernadette