Creating Alerts
SQL-DMO will be removed in the next version of Microsoft SQL Server. Avoid using SQL-DMO feature in new development work, and plan to modify applications that use SQL-DMO.
These examples illustrate creating SQL Server Agent alerts.
A SQL Server Agent alert has, at least, a name and a definition of an event that raises the alert. When using SQL Distributed Management Objects (SQL-DMO) to create SQL Server Agent alerts:
Create an Alert object.
Set the Name property.
Set either the MessageID, PerformanceCondition, or Severity property to indicate the event that will raise the alert.
Add the Alert object to an Alerts collection.
Setting more than a single event property causes an error.
Examples
A. Creating an Alert Based on a SQL Server Error
This example illustrates creating a SQL Server Agent alert raised when a Microsoft SQL Server error condition occurs. The alert created is constrained to be raised only if the error condition occurs in the Northwind database.
' Create an Alert object and set its Name property.
Dim oAlert As New SQLDMO.Alert
oAlert.Name = "Max filesize exceeded"
' Error 5176: The file '%.*ls' has been expanded beyond its
' maximum size to prevent recovery from failing. Contact the
' system administrator for further assistance.
oAlert.MessageID = 5176
oAlert.DatabaseName = "Northwind"
' Create the alert by adding the Alert object to its containing
' collection. Note: Create and connect of SQLServer object used
' not illustrated in this example.
oSQLServer.JobServer.Alerts.Add oAlert
B. Creating an Alert Based on a Performance Condition
This example illustrates creating a SQL Server Agent alert raised when a monitored performance counter value is exceeded.
' Create an Alert object and set its Name property.
Dim oAlert As New SQLDMO.Alert
oAlert.Name = "Batch Requests High"
' Performance monitor counter...
' Object: SQLServer:SQL Statistics
' Counter: Batch Requests/sec
' Instance: none
oAlert.PerformanceCondition = _
"SQLServer:SQL Statistics|Batch Requests/sec||>|750"
' Create the alert by adding the Alert object to its containing
' collection. Note: Create and connect of SQLServer object used
' not illustrated in this example.
oSQLServer.JobServer.Alerts.Add oAlert