Using WQL with the WMI Provider for Server Events

event_property

Is a property of an event. Examples include PostTime, SPID, and LoginName. Look up each event listed in WMI Provider for Server Events Classes and Properties to determine which properties it holds. For example, the DDL_DATABASE_LEVEL_EVENTS event holds the DatabaseName and UserName properties. It also inherits the SQLInstance, LoginName, PostTime, SPID, and ComputerName properties from its parent events.

,...n

Indicates that event_property can be queried multiple times, separated by commas.

*

Specifies that all properties associated with an event are queried.

event_type

Is any event against which an event notification can be created. For a list of available events, see WMI Provider for Server Events Classes and Properties. Note that event type names correspond to the same event_type | event_group that can be specified when you manually create an event notification by using CREATE EVENT NOTIFICATION. Examples of event type include CREATE_TABLE, LOCK_DEADLOCK, DDL_USER_EVENTS, and TRC_DATABASE.

Note

Certain system stored procedures that perform DDL-like operations can also fire event notifications. Test your event notifications to determine their responses to system stored procedures that are run. For example, the CREATE TYPE statement and sp_addtype stored procedure will both fire an event notification that is created on a CREATE_TYPE event. However, the sp_rename stored procedure does not fire any event notifications. For more information, see DDL Events.

where_condition

Is a WHERE clause query predicate made up of event_property names and logical and comparison operators. The where_condition determines the scope in which the corresponding event notification is registered in the target database. It can also act as a filter to target a particular schema or object from which to query event_type. For more information, see the Remarks section later in this topic.

Only the = operand can be used together with DatabaseName, SchemaName, and ObjectName. Other expressions cannot be used with these event properties.

The where_condition of the WMI Provider for Server Events syntax determines the following:

• The scope by which the provider tries to retrieve the specified event_type: the server level, database level, or object level (the only object currently supported is queue). Ultimately, this scope determines the type of event notification created in the target database. This process called event notification registration.

• The database, schema, and object, where appropriate, on which to register.

The WMI Provider for Server Events uses a bottom-up, first-fit algorithm to produce the narrowest possible scope for the underlying EVENT NOTIFICATION. The algorithm tries to minimize internal activity on the server and network traffic between the instance of SQL Server and the WMI host process. The provider examines the event_type specified in the FROM clause and the conditions in the WHERE clause, and tries to register the underlying EVENT NOTIFICATION with the narrowest possible scope. If the provider cannot register at the narrowest scope, it tries to register at successively higher scopes until a registration finally succeeds. If it reaches the highest scope the server-level) and fails, it returns an error to the consumer.

For example, if DatabaseName='AdventureWorks2008R2' is specified in the WHERE clause, the provider tries to register an event notification in the AdventureWorks2008R2 database. If the AdventureWorks2008R2 database exists and the calling client has the required permissions to create an event notification in AdventureWorks2008R2, the registration is successful. Otherwise, an attempt is made to register the event notification at the server level. The registration succeeds if the WMI client has the required permissions. However, under this scenario, events are not returned to the client until the AdventureWorks2008R2 database has been created.

The where_condition can also act as a filter to additionally limit the query to a specific database, schema, or object. For example, consider the following WQL query:

SELECT * FROM ALTER_TABLE 
WHERE DatabaseName = 'AdventureWorks2008R2' AND SchemaName = 'Sales' 
    AND ObjectType='Table' AND ObjectName = 'SalesOrderDetail'

Depending on the outcome of the registration process, this WQL query may be registered either at the database or server level. However, even if it is registered at the server level, the provider ultimately filters any ALTER_TABLE events that do not apply to the AdventureWorks2008R2.Sales.SalesOrderDetail table. In other words, the provider returns only the properties of the ALTER_TABLE events that occur on that specific table.

If a compound expression such as DatabaseName='AW1' OR DatabaseName='AW2' is specified, an attempt is made to register a single event notification at the server scope instead of two separate event notifications. The registration succeeds if the calling client has permissions.

If SchemaName='X' AND ObjectType='Y' AND ObjectName='Z' are all specified in the WHERE clause, an attempt is made to register the event notification directly on object Z in schema X. The registration succeeds if the client has permissions. Note that currently, object-level events are supported only on queues, and only for the QUEUE_ACTIVATION event_type.

Note that not all events can be queried at any particular scope. For example, a WQL query on a trace event such as Lock_Deadlock, or a trace event group such as TRC_LOCKS, can only be registered at the server level. Similarly, the CREATE_ENDPOINT event and the DDL_ENDPOINT_EVENTS event group can also be registered only at the server level. For more information about the appropriate scope for registering events, see Designing Event Notifications. An attempt to register a WQL query whose event_type can only be registered at the server level is always made at the server level. Registration succeeds if the WMI client has permissions. Otherwise, an error is returned to the client. In some cases, however, you can still use the WHERE clause as a filter for server-level events based on the properties that correspond to the event. For example, many trace events have a DatabaseName property that can be used in the WHERE clause as a filter.

Server-scoped event notifications are created in the master database and can be queried for metadata by using the sys.server_event_notifications catalog view.

Database-scoped or object-scoped event notifications are created in the specified database and can be queried for metadata by using the sys.event_notifications catalog view. (You must prefix the catalog view with the corresponding database name.)

A. Querying for events at the server scope

SELECT * FROM SERVER_MEMORY_CHANGE

B. Querying for events at the database scope

SELECT SPID, SQLInstance, DatabaseName FROM DDL_DATABASE_LEVEL_EVENTS 
WHERE DatabaseName = 'AdventureWorks2008R2' 

C. Querying for events at the database scope, filtering by schema and object

SELECT * FROM ALTER_TABLE 
WHERE DatabaseName = 'AdventureWorks2008R2' AND SchemaName = 'Sales' 
    AND ObjectType='Table' AND ObjectName = 'SalesOrderDetail'

Concepts

Other Resources