3 Replies Latest reply on Nov 25, 2014 1:48 PM by mharvey

    Report Writer advanced SQL help

    macadio

      Our DBA was able to create a temp table and get values that I need for a fiscal year to SUM the values for the whole year period into one value (I.E July1-July1 with a value of uptime %99.98766555 etc..)

      This code works fine in MS SQL Studio version 2008 I believe. However, when I try and drop this same code into the report designer in solarwinds it bombs and doesn't work. Unfortunately my DBA is busy on other projects and his response is that it's basic SQL so it should work or at least it does in MS Studio but not in Solarwinds so if one of you SQL solarwinds Guru's can look at the code and tell me what's wrong.

       

      Thanks!

       

       

      SELECT  TOP 10000 CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth,
      Nodes.NodeID AS NodeID,
      Nodes.VendorIcon AS Vendor_Icon,
      Nodes.Caption AS NodeName,
      Nodes.IP_Address AS IP_Address,
      AVG(ResponseTime.Availability) AS AVERAGE_of_Availability
      INTO #yearly_avg_temp
      FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

      WHERE
      ( DateTime BETWEEN 41454 AND 41818 )
      AND  
      (
        (Nodes.IP_Address = '<SERVER IP>')
      )
      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101), 
      Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address
      ORDER BY SummaryMonth ASC, 5 ASC;

      SELECT avg(average_of_availability) as [Yearly Average Uptime]

      FROM #yearly_avg_temp;

      if object_id('tempdb..#yearly_avg_temp') is not null
      drop table #yearly_avg_temp;

        • Re: Report Writer advanced SQL help
          mharvey

          Are you using the web based report writer, or the Report Writer application on the server?  My guess is that it doesn't understand how to connect to the tempdb in the second SQL select statement in the report query.  You'd probably have to do something before Select avg(average.... like Use tempDB in order to tell the software to look at another database for that information.

            • Re: Report Writer advanced SQL help
              macadio

              Not using the web report writer, Im using the Orion Report Writer on the server. Thanks for that pointer. I think it might be bombing before that because what I did was commented out the bottom section to try and use "break points" to see where the code is stopping and it seemed to stop at the "INTO" section because if I comment that INTO out.. it pulls the basic info for that node. Which I just did a cut a paste from an actual Orion node report that I knew worked. I just did the "Show SQL" option since I know that worked. And had my DBA work from that point. But maybe I will try playing a bit more with the break points just to make sure.