SQL-DMO
Handling Raised Alerts (Notification)
These examples illustrate configuring SQL Server Agent alerts so that operators can be notified or administrative action can be taken.
In response to raised alerts, SQL Server Agent can notify operators or run jobs or both.
Examples
A. Notifying an Operator when an Alert is Raised
This example illustrates creating a SQL Server Agent operator notification as a response to a raised alert.
' Get the Alert object referencing the targeted alert. Note: Create and
' connect of SQLServer object used not illustrated in this example.
Dim oAlert As SQLDMO.Alert
Set oAlert = oSQLServer.JobServer.Alerts("Batch Requests High")
' Configure the alert response, adding operator notification by email
' and network popup message.
oAlert.AddNotification "anned", _
SQLDMONotify_Email Or SQLDMONotify_NetSend
B. Running a Job when an Alert is Raised
This example illustrates altering a SQL Server Agent alert, configuring it for job execution as a response.
Dim oAlert As SQLDMO.Alert
Dim oJob As SQLDMO.Job
' Get the Alert object referencing the targeted alert, and the Job
' object referencing the job run in response. Use the Job object to
' determine the job identifier, uniquely identifying a SQL Server
' Agent job. Note: Create and connect of SQLServer object used not
' illustrated in this example.
Set oAlert = oSQLServer.JobServer.Alerts("Max filesize exceeded")
Set oJob = oSQLServer.JobServer.Jobs("Backup_Northwind_Filegroups")
' Modify the alert by setting the JobID property of the Alert object
' and committing the change.
oAlert.BeginAlter
oAlert.JobID = oJob.JobID
oAlert.DoAlter