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

Reply
  • 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('')

Children