This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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 ? 

Reply
  • 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 ? 

Children
  • 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.... Slight smile

    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!