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

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