Microsoft® SQL Server® 2012 Bible

416

Part IV: Programming with T-SQL

The sys.messages table includes columns for the message_id, text, severity, and

whether the error should be logged. However, the severity of the RAISERROR command is

used instead of the severity from the sys.messages table, so sys.messages

.severity is moot.

To manage messages in code, use the sp_addmessage system stored procedure:

EXEC sp_addmessage 50001, 16, 'Unable to update %s';

For database projects that may be deployed in multiple languages, the optional @lang

parameter can be used to specify the language for the error message.

If the message already exists, then a replace parameter must be added to the system

stored procedure call, as follows:

EXEC sp_addmessage 50001, 16,

'Update error on %s', @replace = 'replace';

To view the existing custom messages, select from the sys.messages system view:

SELECT *

FROM sys.messages

WHERE message_id > 50000;

Result:

message_id language_id severity is_event_logged text

----------- ----------- -------- --------------- -------------------

50001 1033 16 0 Unable to update %s

To move messages between servers, do one of the following:

■ Save the script that was originally used to load the messages.

■ (^) Use the following query to generate a script that adds the messages:
SELECT 'EXEC sp_addmessage, '
 + CAST(message_id AS VARCHAR(7))
 + ', ' + CAST(severity AS VARCHAR(2))
 + ', '''  + [text] +  ''';'
FROM sys.messages
WHERE message_id > 50000;
Result:

EXEC sp_addmessage, 50001, 16, 'Unable to update %s';
To drop a message, use the sp_dropmessage system stored procedure with the error
number:
EXEC sp_dropmessage 50001;
c16.indd 416c16.indd 416 7/30/2012 5:38:10 PM7/30/2012 5:38:10 PM
http://www.it-ebooks.info