Creating User-Defined Error Messages
Microsoft SQL Server 2000 has a large number of pre-defined messages that are returned to the user when specific events occur. Each message has a severity level. Level 10 are simply for information purposes, whereas level 25 denotes a hardware failure. Some of these errors are written to the NT Event log as well as the Microsoft SQL Server 2000 errorlog.
All of these messages are stored in the master..sysmessages table.
In addition to these messages you can define your own, with a severity level and with the ability to write to the Windows Event log.
Viewing Existing Messages
To view the existing messages you can query the sysmessages table, or you can display them through Enterprise Manager.
To search for an existing message
- From Enterprise Manager’s Tools menu, select Manage SQL Server Messages
- In the Search dialogue box, type in the message text or the number of the message you want to search for
To create your own user-defined message
- Click the Message tab
- Click New and enter the details of the message
- Click OK
sp_addmessage Stored Procedure
You can also add a message through the query interface using the sp_addmessage stored procedure. This stored procedure adds a new error message to the sysmessages table directly.
sp_addmessage [@msgnum =] msg_id, [@severity =] severity, [@msgtext =] 'msg' [, [@lang =] 'language'] [, [@with_log =] 'with_log'] [, [@replace =] 'replace']
sp_addmessage 50001, ,17, "User %s has attempted to change Order No %d by an alarming amount"