0 Replies Latest reply on May 23, 2014 3:10 PM by sgenius

    SQL Error: Arithmetic overflow error converting expression to data type int

    sgenius

      Hi Guys,

       

      I'm here again.

      I'm trying to write a generic SQL code that would take the current date and time of the Solarwinds server, verify if it's a weekday, and generate a report based on business hours of that day, that is, 8:00am to 6:00pm.

      But I keep getting the error message "SQL Error: Arithmetic overflow error converting expression to data type int. "

       

      Honestly, I do not know how to go about it.

      See the codes below.

       

      IF (DatePart (weekday, GETDATE()) <> 7) AND (DatePart(weekday, GETDATE()) <> 1)

      SELECT Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Nodes.Caption AS NodeName, Nodes.Location AS Location, Interfaces.Caption AS InterfaceName, AVG(InterfaceTraffic_Detail.In_Averagebps) AS Average_Receive_bps, MAX(InterfaceTraffic_Detail.In_Maxbps) AS MAX_Receive_bps,

      AVG(Case InBandwidth
                  When 0 Then 0
                  Else (In_Averagebps/InBandwidth) * 100
                  End) AS AVERAGE_of_Recv_Percent_Utilization, AVG(ResponseTime_Detail. PercentLoss) AS AVERAGE_of_PercentLoss,AVG(InterfaceAvailability_Detail.Availability) AS AVERAGE_of_Availability

      FROM dbo.InterfaceAvailability_Detail INNER JOIN dbo.InterfaceTraffic_Detail ON
      (InterfaceAvailability_Detail.NodeID = InterfaceTraffic_Detail.NodeID) AND (InterfaceAvailability_Detail.InterfaceID = InterfaceTraffic_Detail.InterfaceID)

      INNER JOIN

      dbo.Interfaces ON (InterfaceAvailability_Detail.NodeID = Interfaces.NodeID) AND (InterfaceAvailability_Detail.InterfaceID = Interfaces.InterfaceID)

      INNER JOIN

      dbo.Nodes ON(InterfaceAvailability_Detail.NodeID = Nodes.NodeID)

      INNER JOIN

      dbo.ResponseTime_Detail ON (InterfaceAvailability_Detail.NodeID = ResponseTime_Detail.NodeID)


      WHERE

      (DatePart(Hour, GETDATE()) >= 8) AND (DatePart(Hour, GETDATE()) <= 18)

      AND

      (
        (Nodes.Caption LIKE '%%BranchRouter%%') AND
        (Interfaces.Caption LIKE '%%Tunnel%%Spoke DMVPN%%') AND
        )


      GROUP BY Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.Location, Interfaces.Caption

      ORDER BY 3 ASC

       

      ==========================================================================================

       

      I made use of the Orion Database Manager to view the tables that I need and the columns that I require, as well as to understand the matching columns that exists amongst the tables.

      I would implore you to copy the code and run it on your own work environment to see what the output would be like. You do have to make changes to the red colour text in the codes so as to fit into what you want to spool from your work environment's database.

       

      My issue is just the SQL error; how do I go about it?

      Your comments would be highly appreciated.

       

      Regards,

       

      David.