Creating Alerts

SQL-DMO

SQL-DMO

Creating Alerts

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

See Also

Alert Object

PerformanceCondition Property

MessageID Property

Severity Property