{"id":862,"date":"2006-04-25T11:32:00","date_gmt":"2006-04-25T11:32:00","guid":{"rendered":"\/blogs\/bobb\/post\/Using-WMI-and-SQL-Agent-Alerts-(part-2).aspx"},"modified":"2013-01-04T00:00:15","modified_gmt":"2013-01-04T08:00:15","slug":"using-wmi-and-sql-agent-alerts-part-2","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/","title":{"rendered":"Using WMI and SQL Agent Alerts (part 2)"},"content":{"rendered":"

\nThis one ought to be titled "Your error message is such a tease".\n<\/p>\n

\nI'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.\n<\/p>\n

\nSo, in SP1, my perfectly working WMI alerts stopped working. Looking at the job history, I found the following:\n<\/p>\n

\nUnable 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.\n<\/p>\n

\nGreat. 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<\/a>, 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>– A List of bugs that have been been fixed" and found IT had been updated with a reference to "KB 915845 <\/a>– 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:\n<\/p>\n

\n$(ESCAPE_SQUOTE(token))
\n$(ESCAPE_DQUOTE(token))
\n$(ESCAPE_RBRACKET(token))
\n$(ESCAPE_NONE(token))\n<\/p>\n

\nThere'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….\n<\/p>\n

\nTurns out this error occurred because I had SSMS working in SQLCMD mode by default (see "What's fixed in SP1 blog entry<\/a>") 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:\n<\/p>\n

\nINSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
\n  VALUES (getdate(), N'$(ESCAPE_NONE(WMI(TextData)))')\n<\/p>\n

\nSo 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,28],"tags":[],"class_list":["post-862","post","type-post","status-publish","format-standard","hentry","category-event-notifications","category-sql-server-2005"],"yoast_head":"\nUsing WMI and SQL Agent Alerts (part 2) - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using WMI and SQL Agent Alerts (part 2) - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2006-04-25T11:32:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T08:00:15+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/\",\"name\":\"Using WMI and SQL Agent Alerts (part 2) - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2006-04-25T11:32:00+00:00\",\"dateModified\":\"2013-01-04T08:00:15+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Event Notifications\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/event-notifications\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Using WMI and SQL Agent Alerts (part 2)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using WMI and SQL Agent Alerts (part 2) - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/","og_locale":"en_US","og_type":"article","og_title":"Using WMI and SQL Agent Alerts (part 2) - Bob Beauchemin","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/","og_site_name":"Bob Beauchemin","article_published_time":"2006-04-25T11:32:00+00:00","article_modified_time":"2013-01-04T08:00:15+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/","name":"Using WMI and SQL Agent Alerts (part 2) - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2006-04-25T11:32:00+00:00","dateModified":"2013-01-04T08:00:15+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/using-wmi-and-sql-agent-alerts-part-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Event Notifications","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/event-notifications\/"},{"@type":"ListItem","position":3,"name":"Using WMI and SQL Agent Alerts (part 2)"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/862","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=862"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/862\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=862"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}