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

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('')

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 . yes the XML Path flattens the return  to 1 return line of data. 

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>

 

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

As HTML