Alert macro not working when no results..argh

bluefunelemental

I am attempting to add which was the last (or better a list of) Sql Agent jobs which have caused the Application Template to go down.

None of these work unless there is returned rows and just displays the ${SQL: SELECT.... in our alert email. Everything works fine in MSSSMS or in Web reporting.

${SQL: SELECT Name FROM Orion.dbo.APM_SqlBbJobAlertsData WHERE JobStatus NOT LIKE 'Up' AND NodeID = ${N=SwisEntity;M=Node.NodeID} ORDER BY LastRunDate Desc}

or

${SQL: SELECT Name FROM Orion.dbo.APM_SqlBbJobAlertsData WHERE LastRunStatus <> 1 AND NodeID = ${N=SwisEntity;M=Node.NodeID} ORDER BY LastRunDate Desc}

or

${SQL: SELECT Name FROM Orion.dbo.APM_SqlBbJobAlertsData WHERE IsNull(LastRunStatus,1) <> 1 AND NodeID = ${N=SwisEntity;M=Node.NodeID} ORDER BY LastRunDate Desc}

or

${SQL: SELECT CASE WHEN Name IS NULL THEN '' ELSE Name END AS [Name] FROM Orion.dbo.APM_SqlBbJobAlertsData WHERE LastRunStatus <> 1 AND NodeID = ${N=SwisEntity;M=Node.NodeID} ORDER BY LastRunDate Desc}

anyone have any ideas?

njoylif

ah yes, good times...

try

${SQL: SELECT ISNULL(Name,'None') FROM Orion.dbo.APM_SqlBbJobAlertsData WHERE JobStatus NOT LIKE 'Up' AND NodeID = ${N=SwisEntity;M=Node.NodeID} ORDER BY LastRunDate Desc}

"None" can be replaced with anything, even empty set ''