Using WMI and SQL Agent Alerts (part 2)

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.

5 thoughts on “Using WMI and SQL Agent Alerts (part 2)

  1. Hi Bob,

    I had the same issue with that but in addition I have a question: I am using the token A-DBN in an alert for a database. The alert should run a job which schould backup the log if the log is 95 percent full. Everything is fine in the setup of the alert and the job, BUT the token is always replaced with "master" and not the database which is used for the alert. Did you recognize the same phenomenon or do you have any idea what the reson could be for this behaviour? Bug? User error? If you have any hint, I would highly appreciate it!

    Regards
    Nobs

  2. Hi Bob,

    The new macro requirement for tokens is in the April 2006 Books Online. If you search BOL for

    job step macro

    you’ll get the article "Using Tokens in Job Steps" (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/105bbb66-0ade-4b46-b8e4-f849e5fc4d43.htm) which explains the new requirement and contains a link to KB 915845.

    You should realize that just adding the ESCAPE_NONE macro does nothing to protect against the security vulnerability addressed by this change. Be particularly careful using (A-MSG), which an attacker has more control over than some of the other tokens.

    Steve

  3. What Nob mentions about master being the value all the time for datbase looks like a bug and is not related to new syntax.

    We have a new bug opened to track this.

    Thanks,
    Gops Dwarak, Microsoft

  4. The script in the KB article is now fixed to run in SQL CMD mode.

    Also, we are planning to add more index entry to BOL for SP2, to facilitate user search for information for the new macros.

    Thanks for reporting them. We appreciate your feedback!

  5. I have experienced similar behavior to Nobs but found through testing that the name of the database that would be passed in was the one in which my transaction was running (not the one whose log got full). Running a select from databasefiles in msdb into another database called FullLogTest would fill up the FullLogTest T Log but then the alert would pass in msdb for the database (which is in simple recovery). I changed the context and ran my transaction in the FullLogTest database and it passed in that database name and backed up the transaction log. After reading Nobs post, I decided to run my transaction while in the master db (still selecting from msdb into FullLogTest) and yes, my job kicked off and backed up the log for master!

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.