Skip to content

Commit db8f51f

Browse files
rwestMSFTrwestMSFT
committed
Refresh article and fix issue 8093
1 parent 992c582 commit db8f51f

1 file changed

Lines changed: 69 additions & 77 deletions

File tree

‎docs/ssms/agent/create-a-wmi-event-alert.md‎

Lines changed: 69 additions & 77 deletions
Original file line numberDiff line numberDiff line change
@@ -1,91 +1,83 @@
11
---
2-
description: "Create a WMI Event Alert"
32
title: "Create a WMI Event Alert"
4-
ms.custom: seo-lt-2019
5-
ms.date: 01/19/2017
3+
description: "Create a WMI Event Alert"
4+
author: markingmyname
5+
ms.author: maghan
6+
ms.reviewer: randolphwest
7+
ms.date: 09/20/2022
68
ms.prod: sql
7-
ms.prod_service: sql-tools
89
ms.technology: ssms
910
ms.topic: conceptual
10-
helpviewer_keywords:
11+
ms.custom: seo-lt-2019
12+
helpviewer_keywords:
1113
- "WMI event alerts [SQL Server Management Studio]"
12-
ms.assetid: b8c46db6-408b-484e-98f0-a8af3e7ec763
13-
author: markingmyname
14-
ms.author: maghan
15-
ms.reviewer: ""
1614
monikerRange: "= azuresqldb-mi-current || >= sql-server-2016"
1715
---
18-
# Create a WMI Event Alert
16+
# Create a WMI event alert
17+
1918
[!INCLUDE [SQL Server SQL MI](../../includes/applies-to-version/sql-asdbmi.md)]
2019

2120
> [!IMPORTANT]
2221
> On [Azure SQL Managed Instance](/azure/sql-database/sql-database-managed-instance), most, but not all SQL Server Agent features are currently supported. See [Azure SQL Managed Instance T-SQL differences from SQL Server](/azure/sql-database/sql-database-managed-instance-transact-sql-information#sql-server-agent) for details.
2322
24-
This topic describes how to a [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent alert that is raised when a specific [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] event occurs that is monitored by the WMI Provider for Server Events in [!INCLUDE[ssnoversion](../../includes/ssnoversion-md.md)] by using SQL Server Management Studio or [!INCLUDE[tsql](../../includes/tsql-md.md)].
25-
23+
This article describes how to a [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent alert that is raised when a specific [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] event occurs that is monitored by the WMI Provider for Server Events in [!INCLUDE[ssnoversion](../../includes/ssnoversion-md.md)] by using SQL Server Management Studio or [!INCLUDE[tsql](../../includes/tsql-md.md)].
24+
2625
For information about the using the WMI Provider to monitor [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] events, see [WMI Provider for Server Events Classes and Properties](../../relational-databases/wmi-provider-server-events/wmi-provider-for-server-events-concepts.md). For information about the permissions necessary to receive WMI event alert notifications, see [Select an Account for the SQL Server Agent Service](../../ssms/agent/select-an-account-for-the-sql-server-agent-service.md). For more information about WQL, see [Using WQL with the WMI Provider for Server Events](../../relational-databases/wmi-provider-server-events/using-wql-with-the-wmi-provider-for-server-events.md).
27-
## <a name="BeforeYouBegin"></a>Before You Begin
28-
29-
### <a name="Restrictions"></a>Limitations and Restrictions
30-
31-
- 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.
32-
33-
- 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.
34-
35-
- Only WMI namespaces on the computer that runs [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent are supported.
36-
37-
### <a name="Security"></a>Security
38-
39-
#### <a name="Permissions"></a>Permissions
40-
By default, only members of the **sysadmin** fixed server role can execute **sp_add_alert**.
41-
42-
## <a name="SSMSProcedure"></a>Using SQL Server Management Studio
43-
44-
#### To create a WMI event alert
45-
46-
1. In **Object Explorer,** click the plus sign to expand the server where you want to create a WMI event alert.
47-
48-
2. Click the plus sign to expand **SQL Server Agent**.
49-
50-
3. Right-click **Alerts** and select **New Alert**.
51-
52-
4. In the **New Alert** dialog box, in the **Name** box, enter a name for this alert.
53-
54-
5. Check the **Enable** check box to enable the alert to run. By default, **Enable** is checked.
55-
56-
6. In the **Type** list, select **WMI event alert**.
57-
58-
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.
59-
60-
8. In the **Query** box, specify the WQL statement that identifies the event that this alert responds to.
61-
62-
9. Click **OK**.
63-
64-
## <a name="TsqlProcedure"></a>Using Transact-SQL
65-
66-
#### To create a WMI event alert
67-
68-
1. In **Object Explorer**, connect to an instance of [!INCLUDE[ssDE](../../includes/ssde_md.md)].
69-
70-
2. On the Standard bar, click **New Query**.
71-
72-
3. Copy and paste the following example into the query window and click **Execute**.
73-
74-
```
75-
-- creates a WMI event alert that retrieves all event properties for any ALTER_TABLE event that occurs on table AdventureWorks2012.Sales.SalesOrderDetail
76-
-- This example assumes that the message 54001 already exists.
77-
USE msdb ;
78-
GO
79-
80-
EXEC dbo.sp_add_alert
81-
@name = N'Test Alert 2',
82-
@message_id = 54001
83-
@notification_message = N'Error 54001 has occurred on the Sales.SalesOrderDetail table on the AdventureWorks2012 database.',
84-
@wmi_namespace = '\\.\root\Microsoft\SqlServer\ServerEvents\,
85-
@wmi_query = N'SELECT * FROM ALTER_TABLE
86-
WHERE DatabaseName = 'AdventureWorks2012' AND SchemaName = 'Sales'
87-
AND ObjectType='Table' AND ObjectName = 'SalesOrderDetail'';
88-
GO
89-
```
90-
91-
For more information, see [sp_add_alert (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-add-alert-transact-sql.md).
26+
27+
## <a id="Restrictions"></a> Limitations and restrictions
28+
29+
- 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.
30+
31+
- 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.
32+
33+
- Only WMI namespaces on the computer that runs [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] Agent are supported.
34+
35+
## <a id="Permissions"></a> Permissions
36+
37+
By default, only members of the **sysadmin** fixed server role can execute `sp_add_alert`.
38+
39+
## <a id="SSMSProcedure"></a> Use SQL Server Management Studio
40+
41+
1. In **Object Explorer,** select the plus sign to expand the server where you want to create a WMI event alert.
42+
43+
1. Select the plus sign to expand **SQL Server Agent**.
44+
45+
1. Right-click **Alerts** and select **New Alert**.
46+
47+
1. In the **New Alert** dialog box, in the **Name** box, enter a name for this alert.
48+
49+
1. Check the **Enable** check box to enable the alert to run. By default, **Enable** is checked.
50+
51+
1. In the **Type** list, select **WMI event alert**.
52+
53+
1. 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.
54+
55+
1. In the **Query** box, specify the WQL statement that identifies the event that this alert responds to.
56+
57+
1. Select **OK**.
58+
59+
## <a id="TsqlProcedure"></a> Use Transact-SQL
60+
61+
1. In **Object Explorer**, connect to an instance of [!INCLUDE[ssDE](../../includes/ssde_md.md)].
62+
63+
1. On the Standard bar, select **New Query**.
64+
65+
1. Copy and paste the following example into the query window and select **Execute**.
66+
67+
```sql
68+
USE msdb;
69+
GO
70+
71+
EXEC dbo.sp_add_alert @name = N'Test Alert 2',
72+
@message_id = 54001,
73+
@notification_message = N'Error 54001 has occurred on the Sales.SalesOrderDetail table on the AdventureWorks2012 database.',
74+
@wmi_namespace = '\.\root\Microsoft\SqlServer\ServerEvents',
75+
@wmi_query = N'SELECT * FROM ALTER_TABLE
76+
WHERE DatabaseName = ''AdventureWorks2012'' AND SchemaName = ''Sales''
77+
AND ObjectType=''Table'' AND ObjectName = ''SalesOrderDetail''';
78+
GO
79+
```
80+
81+
## Next steps
82+
83+
- [sp_add_alert (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-add-alert-transact-sql.md)

0 commit comments

Comments
 (0)