MACRO SQL ERROR - Incorrect syntax near the keyword 'AS'.

Hi, like what you trying to create but the query need some changes.

Below query should give you what you want:

SELECT 
M.FullName + ' (' + S.StatusName + ')' +', '
FROM ContainerMemberSnapshots AS M
INNER JOIN StatusInfo AS S on M.[Status]=S.StatusId
WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = 1
FOR XML PATH('')

If above give you what you want below code is the "variable" to add in the alert email. 

${SQL: SELECT M.FullName + ' (' + S.StatusName + ')' +', ' FROM ContainerMemberSnapshots AS M INNER JOIN StatusInfo AS S on M.[Status]=S.StatusId WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = ${N=SwisEntity;M=ContainerID} FOR XML PATH('')}

I've added a Column name to the Select line and the end called 'COLN'  and the query works when I remove the statement of 'FOR XML PATH('')'. But the only returns only 1 component back.  When I add the "FOR XML PATH('')" statement I get the  MACRO SQL ERROR - No column name was specified for column 1 of '0tlouona_hdf'.  Error.  

Also in MSSMS the result look like this : 

So when in run this within SolarWinds it seems like it don't know what to do with the top line of the return, here my query 

SELECT (upper(M.FullName) + ' (' + [data()].StatusName + ')' + ', ') AS 'BR'
FROM SolarWindsOrion.dbo.ContainerMemberSnapshots AS M
INNER JOIN SolarWindsOrion.dbo.StatusInfo AS [data()] on M.[Status] = [data()].StatusId
WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = 1
FOR XML PATH('')

yzyrlr1, just a shot in the dark but what if you aliased out the column as 'XML'? I'm not super confident it'll make a difference, but perhaps it might.

Howabout this? Removed subquery and aliased everything.

${SQL: SELECT m.FullName + ' (' + s.StatusName + ')' + ', ' AS Output1 FROM ContainerMemberSnapshots AS m INNER JOIN StatusInfo AS s ON m.Status = s.StatusId WHERE m.Status != 0 AND m.Status != 1 AND m.Status != 9 AND m.Status != 27 AND m.ContainerID = ${N=SwisEntity;M=ContainerID} FOR XML PATH('')}

Sorry for taking some time. Below query is now testes and works fine for me. The alert trigger object should be "Groups" for the group-id variable included in the query to be correct.

Use below in a html formatted email and you will get each item on a separate row. 

${SQL: SELECT REPLACE(Replace((SELECT(SELECT M.FullName + ' (' + S.StatusName + ')' +'</BR> ' FROM ContainerMemberSnapshots AS M INNER JOIN StatusInfo AS S on M.[Status]=S.StatusId WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = ${N=SwisEntity;M=ContainerID} FOR XML PATH('') ) AS N	), '<', '<'), '>', '>') AS [Result]}

Hi there I tried the code in my Alert email notification and its doesn't list the data terrifically, its shows the "<br>"  the html line brake logic, but still list everything on one continuous line.   

here an examples

ABZYMCCM01 (Critical) </br> XYZAsccm20 (Warning) </br>

 

here an examples

ABZYMCCM01 (Critical) </br> XYZAsccm20 (Warning) </br>

 

Did you send the alert email as "html" and not "plain text"?

As HTML 

yzyrlr1, sounds like the recipient is unable to receive HTML formatted emails or something is stripping it out. I would check to see if the recipient can receive HTML formatted emails, and then see if there's something in between that is stripping out the HTML properties because of exceeding X number of hyperlinks, etc. 

ok