Creating Alerts (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-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"