Lately I've been working on using WMI events in conjunction with SQLAgent alerts and jobs to notify operators, queue up notifications for the DBA's resolution offline, and other uses. Setting up the alert and the job is fairly straightforward, where I stumbled at first is referencing WMI variables (and even knowing what variables were available) in the job that processes the WMI event. The varaibles are doc'd in BOL in conjunction with event notifications, which is really what the WMI provider for Events uses underneath.
You use a WQL query that references the event to set up the WMI Alert, using "SELECT * FROM <my_event>" syntax (BOL example is SELECT * FROM DEADLOCK_EVENTS). And reference in job by using a WMI variable, like so:
— BOL example
INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
VALUES (getdate(), N''$(WMI(TextData))''')
So, when you set all this up, why doesn't it work? It always produces "TextData not available". Hmmm…The "magic switch" to success is found under SQL Server Agent "Properties" menu entry under SSMS. You need to turn on (in the Alert System tab, at the bottom of the page) "Replace tokens for all job responses to alerts". They aren't WMI variables, they are WMI tokens. Success! Took me a while to find that one…
But, in SP1 it "breaks" again. Sigh. More about this in next blog entry.