AUTOMATING ADMINISTRATION (Page 5)

RAISERROR

You can raise error messages with the RAISERROR command which returns a user-defined error message and sets a system flag to record that an error has occurred. RAISERROR can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message.

Syntax

RAISERROR ({msg_id | msg_str}{, severity, state}
[, argument
[,...n]] )
[WITH option[,...n]]

image14 AUTOMATING ADMINISTRATION
       (Page 5)

For example

For instance the following statement will raise the example error message defined previously.

raiserror (50001, 17,10,@username,@OrderNo) with log

Set Up Alerts

SQLServerAgent is continually monitoring the NT Event Log (Application Log) for errors that have been written there from SQL Server.

You can set up an Alert to react to anything logged in this way.

Types of Alerts

There are two main kinds of alert in SQL Server 2000:

  • Event Alerts. Event Alerts are where SQL Server 2000 responds to a specific error number, severity level or message text string.
  • Performance Condition Alert. This type of alert allows you to respond to a performance monitor counter exceeding, falling below or equalling a specific value.

Alert Responses

The Alert may generate a notification (email, pager or net send) or start a job, or both.

Alert Delay and Retry Interval

One important consideration with alerts is how long to set the built-in delay between the alert re-firing. If there was no delay, many users could encounter the same error setting off hundreds of job requests. The default delay of one minute should prevent this most of the time but you should experiment with different values to make it optimal.

To create a new alert

Through Enterprise Manager you can create an alert by performing the following steps:

  • Find and open the Management folder in Enterprise Manager then expand the SQLServerAgent folder, right-click Alerts and choose New Alert
  • Type in a name and select the type of alert (Event or Performance Condition)
  • Enter the details of the alert and any response then click OK

image15 AUTOMATING ADMINISTRATION
       (Page 5)