Create a WMI Event Alert

This topic describes how to a SQL Server Agent alert that is raised when a specific SQL Server event occurs that is monitored by the WMI Provider for Server Events in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

For information about the using the WMI Provider to monitor SQL Server events, see WMI Provider for Server Events Classes and Properties. For information about the permissions necessary to receive WMI event alert notifications, see Select an Account for the SQL Server Agent Service. For more information about WQL, see Using WQL with the WMI Provider for Server Events.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To create a WMI event alert, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• SQL Server Management Studio provides an easy, graphical way to manage the entire alerting system and is the recommended way to configure an alert infrastructure.

• Events generated with xp_logevent occur in the master database. Therefore, xp_logevent does not trigger an alert unless the @database_name for the alert is 'master' or NULL.

• Only WMI namespaces on the computer that runs SQL Server Agent are supported.

Security

Permissions

By default, only members of the sysadmin fixed server role can execute sp_add_alert.

[Top]

Using SQL Server Management Studio

To create a WMI event alert

1. In Object Explorer, click the plus sign to expand the server where you want to create a WMI event alert.

2. Click the plus sign to expand SQL Server Agent.

3. Right-click Alerts and select New Alert.

4. In the New Alert dialog box, in the Name box, enter a name for this alert.

5. Check the Enable check box to enable the alert to run. By default, Enable is checked.

6. In the Type list, select WMI event alert.

7. Under WMI event alert definition, in the Namespace box, specify the WMI namespace for the WMI Query Language (WQL) statement that identifies which WMI event will trigger this alert.

8. In the Query box, specify the WQL statement that identifies the event that this alert responds to.

9. Click OK.

[Top]

Using Transact-SQL

To create a WMI event alert

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

   -- creates a WMI event alert that retrieves all event properties for any ALTER_TABLE event that occurs on table AdventureWorks2012.Sales.SalesOrderDetail
   -- This example assumes that the message 54001 already exists.
   USE msdb ;
   GO
   
   EXEC dbo.sp_add_alert
       @name = N'Test Alert 2',
       @message_id = 54001
       @notification_message = N'Error 54001 has occurred on the Sales.SalesOrderDetail table on the AdventureWorks2012 database. Please see the following information…',
       @wmi_namespace = '\\.\root\Microsoft\SqlServer\ServerEvents\,
       @wmi_query = N'SELECT * FROM ALTER_TABLE 
   WHERE DatabaseName = 'AdventureWorks2012' AND SchemaName = 'Sales' 
       AND ObjectType='Table' AND ObjectName = 'SalesOrderDetail'';
   GO
   

For more information, see sp_add_alert (Transact-SQL).

[Top]