Gathering and Filtering Data for the SQL Server Event Provider
When you define the Microsoft SQL Server event provider, you must define a query that gathers event data. You can optionally define a query that does postprocessing on the tables that you gather data from.
Gathering Data
The Transact-SQL query you define for gathering data can be any Transact-SQL query that returns data that exactly matches the fields in the destination event class. For example, if you defined an event class with two fields, StockSymbol and StockPrice, the only requirement for the query that gathers events is that it returns only two columns of data that match name and data type of the StockSymbol and StockPrice fields. An example of such a query follows:
SELECT StockSymbol, StockPrice
FROM StockDB.dbo.StockTable;
You can execute a stored procedure for the event-gathering query.
Filtering Data
If your SELECT statement could potentially return a large volume of data, and some of that data is not valid event data, you can filter the query. For example, you can write a query that returns stock data containing only those stocks for which subscribers have created subscriptions:
SELECT StockSymbol, StockPrice
FROM StockDB.dbo.StockTable
WHERE StockSymbol IN
(SELECT DISTINCT StockSymbol FROM dbo.StockSubscriptions);
This query returns only the stock data that can potentially generate notifications.
Postprocessing
The SQL Server event provider supports a postprocessing query so you can clean up or alter data after generating events. For example, you might have a column that has a value of 0 before generating events, but that you want to have a value of 1 after generating events. To update the table, you might write a query like this:
UPDATE StockDB.dbo.StockTable
SET EventUsage = 1
WHERE EventUsage = 0;
The postprocessing query runs in the same transaction as the event-gathering query.
You can execute a stored procedure for the postprocessing query.
See Also
Concepts
Defining a SQL Server Event Provider
Other Resources
Defining Event Providers
Standard Event Providers
SELECT (Transact-SQL)