This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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

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

Parents
  • 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]. 

Reply Children