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

Hi SeaShore, yes this is what I want, but still setting an error in trying to run the code in the Alert message keep getting 

MACRO SQL ERROR - No column name was specified for column 1 of 'w02cs5dn_lwz'.  when I simulate it, but in my SQL Server Studio  it work great.  Any thoughts ? 

Ahh, my bad. Orion does not like if you have "unnamed columns" so we have to add a name for the column:

${SQL: SELECT M.FullName + ' (' + S.StatusName + ')' +', '  AS [N] 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('')}

You probably now get some extra characters in the output. If you don't want them I think we have to wrap above SQL query with another select.

Still getting 

MACRO SQL ERROR - No column name was specified for column 1 of 'zyanuct3_3gu'. with adding AS [N]. 

Maybe I should test my code myself before sending it over....

Another try, wrapped the above query in another select statement to get rid of the strange xml-named-columns

${SQL: SELECT(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('') ) AS N}

I've tried the last update to the code and it didn't work.  I'm trying to install the Orion SDK having problem getting admin access to me PC on this, thanks again for all you help with this, once we can get this figured out it would be a great blessing to me.  thanks again

I've seen that strange error before with the sql macros... and cannot recall what I did to resolve it, if it comes to me I'll reply here!

I've seen that strange error before with the sql macros... and cannot recall what I did to resolve it, if it comes to me I'll reply here!

Ok, thanks