Create a WMI event alert

Artikel
03/03/2023

Applies to: SQL Server Azure SQL Managed Instance

Important

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

This article 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 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.

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.

Permissions

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

Use SQL Server Management Studio

In Object Explorer, select the plus sign to expand the server where you want to create a WMI event alert.
Select the plus sign to expand SQL Server Agent.
Right-click Alerts and select New Alert.
In the New Alert dialog box, in the Name box, enter a name for this alert.
Check the Enable check box to enable the alert to run. By default, Enable is checked.
In the Type list, select WMI event alert.
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.
In the Query box, specify the WQL statement that identifies the event that this alert responds to.
Select OK.

Use Transact-SQL

In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, select New Query.

Copy and paste the following example into the query window and select Execute.

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 AdventureWorks2022 database.',
    @wmi_namespace = '\.\root\Microsoft\SqlServer\ServerEvents',
    @wmi_query = N'SELECT * FROM ALTER_TABLE
WHERE DatabaseName = ''AdventureWorks2022'' AND SchemaName = ''Sales''
AND ObjectType=''Table'' AND ObjectName = ''SalesOrderDetail''';
GO

Next steps

sp_add_alert (Transact-SQL)

Delen via