Using Alert actions to update a custom property

bleearg13

Can the SQL variables used in alerts be used to run an UPDATE query?  I've been attempting to set up an alert that posts a message to the NPM Event Log and runs an UPDATE query to change the value of a custom property and it's not working.  The query seems correct, as I can run it in Enterprise Manager.  I have a custom property called "DepLevel" which I would like to update upon the status of 'Down' for any node:

${SQL:UPDATE Nodes SET DepLevel = '100' WHERE NodeID = ${NodeID} AND Status = '2'}

tdanner

Yes, you can do this. But you have to be sneaky.

The ${SQL} variables are really intended for looking things up in the database, not changing the database. So as a sanity check, the alert engine checks to make sure the SQL statement actually starts with "SELECT".

But we can trick it. SQL Server will accept multiple statements separated by a semicolon. I was able to use this in a "Log to NPM Event Log" action:

${SQL: SELECT 'look the other way';UPDATE Nodes SET MyCustomProperty='hello' WHERE NodeID=${NodeID} }

One little warning: I'm not certain that the SQL variable will always be resolved exactly once per time the alert triggers. For a SELECT or a typical UPDATE like the one in your example, it should cause no problem if it gets run more than once. But if you are doing an INSERT or a relative UPDATE (such as "UPDATE Nodes SET ProblemCount=ProblemCount+1 WHERE NodeID=${NodeID}") in your action, it might get executed more than once, leading to confusion and consternation.