Automating a Response to an Alert

Usually, when an alert occurs, the only information you have to help you understand what caused the alert and the appropriate action to take is contained in the alert message itself. Creating jobs to respond to the alert is time-consuming because you must first parse and analyze the information in the message and then insert the relevant information into Transact-SQL commands. Microsoft® SQL Server™ 2000 replication makes automating response jobs easier by providing additional information about the alert. This information is stored in the sysreplicationalerts system table. In addition to providing detailed information, sysreplicationalerts provides that information already parsed in a form easily used by customized programs.

For example, if the pubs data at Subscriber A fails the validation check, SQL Server triggers alert message 20574 notifying you of that failure. The message you receive may be:

"Subscriber 'A', subscription to article 'authors' in publication 'pubs' failed data validation."

If you create a response job based on the alert message, you must manually parse the Subscriber name, article name, publication name, and error from the message. However, because the Distribution Agent writes that same information in sysreplicationalerts, along with details such as the type of agent, time of the alert, publication database, Subscriber database, and type of publication, the response job can directly query the relevant information from the table. Although the exact row cannot be associated with a specific instance of the alert, the table has a status column, which can be used to keep track of serviced entries. The entries in this table are maintained for the history retention period. 

For example, if you were to create a response job in Transact-SQL that services alert message 20574, you might use the following logic:

declare hc cursor local for select publisher, publisher_db, publication, publication_type, article, subscriber, 
      subscriber_db, alert_id from 
      msdb..sysreplicationalerts where
      alert_error_code = 20574 and status = 0
      for read only
open hc
fetch hc into  @publisher, @publisher_db, @publication, @publication_type, @article, @subscriber, subscriber_db, @alert_id
while (@@fetch_status <> -1)
begin
/* Do custom work  */
/* Update status to 1, which means the alert has been serviced. This prevents subsequent runs of this job from doing this again */
update msdb..sysreplicationalerts set status = 1 where alert_id = @alert_id
 fetch hc into  @publisher, @publisher_db, @publication, @publication_type, @article, @subscriber, @subscriber_db, @alert_id
end
close hc
deallocate hc