Multiple rows with SQL query in alert body?

omavel

Hi guys. Know about the old problem, when you can't just gather multiple rows and put it in alert body in easy way, but dunno how to solve it.

So I have this simple query:

SELECT TOP 10 *
FROM APM_WindowsEvent_Detail
INNER JOIN (SELECT ID as CID FROM APM_Component
WHERE ComponentOrder=1 and ApplicationID = 728) CID
ON ComponentID = CID
ORDER BY TimeGeneratedUtc DESC

Output is also nice:

But if I use this query in a mail body, I get top string only.
Please help to find a workaround which will allow me to get top 10 rows.

m-milligan

You'll need to use a SQL query with the FOR XML PATH('') clause. Try this:

SELECT TOP 10 Convert(varchar,TimeGeneratedUtc)+' '+Message
FROM APM_WindowsEvent_Detail
INNER JOIN (SELECT ID as CID FROM APM_Component
WHERE ComponentOrder=1 and ApplicationID = 728) CID
ON ComponentID = CID
ORDER BY TimeGeneratedUtc DESC
for xml path('')