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

Question

Trying to figure out what I'm doing wrong, or to change SQL coding to a join statement 
 
 Trying to get all the member of group components only that are in an alert state (non-up, not un-managemented. etc) to be listed in and email to the support team. 
 
 here my code but keep getting subject message., can some help me here. 
 
 ${SQL: SELECT StatusName FROM SolarWindsOrion.dbo.StatusInfo WHERE statusid !=1 + '.' + char(10) AS [data()] FROM SolarWindsOrion.dbo.ContainerMemberSnapshots WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = ${N=SwisEntity;M=ContainerID} FOR XML PATH('')}

Seashore · Accepted Answer

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

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

Top Replies