This one ought to be titled "Your error message is such a tease".

I've always liked the fact that SQL Server 2005 error messages are verbose (modulo security considerations for login error for example). I always point out at least one during classes, start to read the message...and about 50 words or so later, tell students to "check out the entire text of the message offline". Excellent! Of course message boxes in applications that display these verbose errors must be made bigger or scrollbars added. Such is life.

So, in SP1, my perfectly working WMI alerts stopped working. Looking at the job history, I found the following:

Unable to start execution of step 1 (reason: The job step contains one or more tokens. For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run.). The step failed.

Great. Except what's the magic macro??? What a tease, everything I need to fix things except for the most vital piece of info. Downloaded the April Books Online update, which had a TON of great new and revised content. But nothing about this problem (well they can't catch everything right away). Then I remembered RTF-readme file. Went to the latest "KB 913090 - A List of bugs that have been been fixed" and found IT had been updated with a reference to "KB 915845 - SQL Server Agent jobs fail when the jobs contain job steps that use tokens after you install SQL Server 2005 Service Pack 1". Yep that's the one!! The magic macros are:

$(ESCAPE_SQUOTE(token))
$(ESCAPE_DQUOTE(token))
$(ESCAPE_RBRACKET(token))
$(ESCAPE_NONE(token))

There's even a nice script that catalogs a procedure to update your existing jobs. What could be easier? Except, attempting to catalog the procedure yields "A fatal scripting error occurred. Incorrect syntax occured while parsing '. Huh? That's too sparse of an error. What now, the saga continues....

Turns out this error occurred because I had SSMS working in SQLCMD mode by default (see "What's fixed in SP1 blog entry") and because there was a token $anything in the code, it was being processed by SQLCMD. Ohhhh. Turned off SQLCMD mode and it worked fine. Jobs fixed and working. The jobstep SQL now looks like this:

INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
  VALUES (getdate(), N'$(ESCAPE_NONE(WMI(TextData)))')

So that's "the whole story". Hope it saves you some grief. That said, I REALLY like the WMI and Event Notification idea a lot. It's like having the database "tell me" when there's a problem that I want to know about. In real-time or queued and saved as I sleep for resolution "at my leisure". My leisure...ha, how about "the next morning"? I'll take that.

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.

After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a “nefarious plot” to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you've deposited EventData into a table that looks like this:

CREATE TABLE ddl_log (
 id int primary key identity,
 data XML
);

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
INSERT ddl_log VALUES(EventData);

This makes your table into a rowset. You could also use a variation of it in your event notification handler, DDL trigger itself. Just leave out the cross apply. I thought I'd seen this before, but can never seem to ever have found it. Now that I've done this, ....learn XML why don't 'ya... There can only be more of it in future. Cheers.

SELECT id, 
 Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType',
 Tab.Col.value('./PostTime[1]','datetime') AS  'PostTime',
 Tab.Col.value('./SPID[1]','nvarchar(50)') AS  'SPID',
 Tab.Col.value('./ServerName[1]','nvarchar(50)') AS  'ServerName',
 Tab.Col.value('./LoginName[1]','nvarchar(50)') AS 'LoginName',
 Tab.Col.value('./UserName[1]','nvarchar(50)') AS 'UserName',
 Tab.Col.value('./DatabaseName[1]','nvarchar(128)') AS 'DatabaseName',
 Tab.Col.value('./SchemaName[1]','nvarchar(128)') AS 'SchemaName',
 Tab.Col.value('./ObjectName[1]','nvarchar(128)') AS 'ObjectName',
 Tab.Col.value('./ObjectType[1]','nvarchar(50)') AS 'ObjectType',
 Tab.Col.value('./TSQLCommand[1]/CommandText[1]','nvarchar(4000)') AS 'CommandText',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULLS','nvarchar(3)') AS 'ANSI_NULLS_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULL_DEFAULT','nvarchar(3)') AS 'ANSI_NULL_DEFAULT_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_PADDING','nvarchar(3)') AS 'ANSI_PADDING_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@QUOTED_IDENTIFIER','nvarchar(3)') AS 'QUOTED_IDENTIFIER_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ENCRYPTED_OPTION','nvarchar(4)') AS 'ENCRYPTED_OPTION'
FROM ddl_log
CROSS APPLY
 data.nodes('/EVENT_INSTANCE') AS Tab(Col)
GO

-- standalone trigger

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @x XML
SET @x = Eventdata()
SELECT
 Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType',
 Tab.Col.value('./PostTime[1]','datetime') AS  'PostTime',
 -- rest of columns deleted for brevity
FROM @x.nodes('/EVENT_INSTANCE') AS Tab(Col)
GO