2 Replies Latest reply on Jul 17, 2015 8:31 AM by lag

    Advanced Alert SQL Macro Error - Incorrect syntax

    lag

      ATTN:

       

      Case #836227

       

       

      I'm having a bear of a time getting this one to work, so I am hoping someone in the community might have some insight into what is making my SQL macro barf all over me.

       

      I'm monitoring file system space on a PILLAR NAS, and in my alert, I need to show what file system is running out of space. If I hard code my ${CustomPollerAssignmentID} & ${CustomPollerStatus.RowID}, the macro works, however when I plug the macro variables in, I get a variety of errors depending on how I construct my query. Also, if I run this in SSMS, the query returns fine. Are there things to consider that might be different in how the Adv Alert SQL Macros handle queries vs SSMS that might help me shed some light on where this is breaking down?

       

      Error 1)

       

      MACRO SQL ERROR - Incorrect syntax near ','.


      Error 2) 

       

      MACRO SQL ERROR - Conversion failed when converting the varchar value '1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 3, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 4, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 5, 50, 51, 52, 6, 7, 8, 9' to data type int.


      Here is one version (produces error 1):

      <b>File System: </b>${SQL:SELECT pName.CP_Value AS Name FROM Nodes n LEFT JOIN (SELECT CustomPollerStatus.Status AS CP_Value ,CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), CustomPollerStatus.RowID)) = 1 then CONVERT(VARCHAR(12), CustomPollerStatus.RowID) else 0 End) AS RowID ,Nodes.NodeID FROM CustomPollerAssignment Inner Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID Inner Join CustomPollerStatus on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID Inner Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid WHERE CustomPollers.UniqueName = 'FSName' AND CustomPollerStatus.Status LIKE '%occalls%') AS pName ON pName.NodeID = n.NodeID LEFT JOIN (SELECT CustomPollerStatus.Status AS CP_Value ,CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), CustomPollerStatus.RowID)) = 1 then CONVERT(VARCHAR(12), CustomPollerStatus.RowID) else 0 End) AS RowID ,Nodes.NodeID ,CustomPollerStatus.CustomPollerAssignmentID FROM CustomPollerAssignment Inner Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID Inner Join CustomPollerStatus on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID Inner Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid WHERE CustomPollers.UniqueName = 'FSFree') AS pFree ON pFree.NodeID = n.NodeID AND pFree.RowID = pName.RowID WHERE pFree.CustomPollerAssignmentID='${CustomPollerAssignmentID}' and pName.RowID = (CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), ${CustomPollerStatus.RowID})) = 1 then CONVERT(VARCHAR(12), ${CustomPollerStatus.RowID}) else 0 End))}


      and an easier look at the query:

      SELECT

        pName.CP_Value AS Name

      FROM

        Nodes n

        LEFT JOIN (

        SELECT

        CustomPollerStatus.Status AS CP_Value

        ,CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), CustomPollerStatus.RowID)) = 1 then CONVERT(VARCHAR(12), CustomPollerStatus.RowID) else 0 End) AS RowID

        ,Nodes.NodeID

        FROM

        CustomPollerAssignment

        Inner Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID

        Inner Join CustomPollerStatus on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID

        Inner Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid

        WHERE

        CustomPollers.UniqueName = 'FSName'

        and CustomPollerStatus.Status LIKE '%occalls%'

        ) AS pName ON pName.NodeID = n.NodeID

        LEFT JOIN (

        SELECT

        CustomPollerStatus.Status AS CP_Value

        ,CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), CustomPollerStatus.RowID)) = 1 then CONVERT(VARCHAR(12), CustomPollerStatus.RowID) else 0 End) AS RowID

        ,Nodes.NodeID

        ,CustomPollerStatus.CustomPollerAssignmentID

        FROM

        CustomPollerAssignment

        Inner Join CustomPollers on CustomPollerAssignment.PollerID = CustomPollers.PollerID

        Inner Join CustomPollerStatus on CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID

        Inner Join Nodes on CustomPollerAssignment.NodeID = Nodes.nodeid WHERE CustomPollers.UniqueName = 'FSFree'

        ) AS pFree ON pFree.NodeID = n.NodeID and pFree.RowID = pName.RowID

       

       

      WHERE

        pFree.CP_Value > 500

        and pFree.CustomPollerAssignmentID='${CustomPollerAssignmentID}'

        and pName.RowID = (CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), ${CustomPollerStatus.RowID})) = 1 then CONVERT(VARCHAR(12), ${CustomPollerStatus.RowID}) else 0 End))

       

      I get error # 2 if I change the last line to:

      pName.RowID = ${CustomPollerStatus.RowID}


      I believe the issue is that some RowID's are not [INT], and it's throwing my query off.


      Any ideas on what I'm messing up here?


        • Re: Advanced Alert SQL Macro Error - Incorrect syntax
          ekis

          Based on the error you got (Error # 2), I am assuming that the content of the ${CustomPollerStatus.RowID} macro is not just a single number. It looks like ${CustomPollerStatus.RowID} has various numbers in it that are comma-separated, as shown in the message you got:

           

          Conversion failed when converting the varchar value '1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 3, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 4, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 5, 50, 51, 52, 6, 7, 8, 9' to data type int.


          The presence of the commas there seem to point out that ${CustomPollerStatus.RowID} is not in numerical format, instead, it can be treated as text.

          Text cannot be converted to an integer.

          Just my 2 cents.

            • Re: Advanced Alert SQL Macro Error - Incorrect syntax
              lag

              I was thinking that other values in the RowID table were the issue as some RowID's for other UnDP's aren't integer or even numerical, AND that I was only getting a single value. But, I think you're onto something in that ${RowID} is returning all the RowID values associated with the UnDP. Back to the drawing board...