AUTOMATING ADMINISTRATION (Page 4)

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

unnamed-file-230 AUTOMATING ADMINISTRATION
       (Page 4)

To create your own user-defined message

  • Click the Message tab
  • Click New and enter the details of the message

unnamed-file-232 AUTOMATING ADMINISTRATION
       (Page 4)

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

Syntax

sp_addmessage [@msgnum =] msg_id,
[@severity =] severity,
[@msgtext =] 'msg'
[, [@lang =] 'language']
[, [@with_log =] 'with_log']
[, [@replace =] 'replace']

unnamed-file-234 AUTOMATING ADMINISTRATION
       (Page 4)

For example

sp_addmessage 50001, ,17, "User %s has attempted to change Order No %d by an alarming amount"