3 Replies Latest reply on Oct 15, 2015 4:01 PM by yayyub

    Custom Report Writer - SQL Query Not Working - Interface Discards

    jbiggley

      I had one of our SQL DBAs write a slick SQL query to grab the interface errors for the past month and give me the average and peak.  (It fulfils a customer reporting requirement).  However, when I past the SQL query into a new advanced SQL report the query just runs and run, never returning data.  If I run it directly against the database using the SQL Server management tools, it runs in under 30 seconds.

      Any ideas?  I've posted the SQL query below.

      declare @FirstDay datetime
      declare @LastDay datetime
      declare @customer varchar(30)

      --Set the customer who want.
      set @customer = 'customer name removed'
      set @FirstDay = convert(varchar,DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),0)),101)
      set @LastDay = convert(varchar,DATEADD(MONTH,DATEDIFF(MONTH,0,getdate()),-1),101)

      SELECT  TOP 10000 Nodes.NodeID as NodeId,
      Nodes.SysName AS BoxName,
      Nodes.CustomerName as Customer,
      InterfaceErrors_Detail.InterfaceID AS InterfaceID,
      Nodes.Caption AS NodeName,
      Nodes.VendorIcon AS Vendor_Icon,
      ROUND(AVG(InterfaceErrors_detail.Out_Discards),2) AS AVERAGE_of_OutDiscardsMonth,
      MAX(InterfaceErrors_detail.Out_Discards) AS MAX_of_OutDiscardsMonth,
      ROUND(AVG(InterfaceErrors_detail.in_Discards),2)  AS AVERAGE_of_InDiscardsMonth,
      MAX(InterfaceErrors_detail.in_Discards)  AS MAX_of_InDiscardsMonth

      INTO #Nodes_table

      FROM
      (Nodes INNER JOIN InterfaceErrors_detail ON (Nodes.NodeID = InterfaceErrors_detail.NodeID)) 
      INNER JOIN InterfaceTraffic ON (InterfaceErrors_Detail.InterfaceID = InterfaceTraffic.InterfaceID
      AND InterfaceTraffic.NodeID = Nodes.NodeID)



      where InterfaceErrors_detail.DateTime >= @FirstDay
      and InterfaceErrors_detail.DateTime <= @LastDay
      and Nodes.CustomerName = @customer

      GROUP BY
      Nodes.NodeID, InterfaceErrors_Detail.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Nodes.SysName, Nodes.CustomerName

      select
              #Nodes_table.NodeID, #Nodes_table.Customer, #Nodes_table.BoxName, #Nodes_table.InterfaceID, #Nodes_table.Vendor_Icon,
              Interfaces.Caption, #Nodes_table.AVERAGE_of_OutDiscardsMonth,#Nodes_table.MAX_of_OutDiscardsMonth,
              #Nodes_table.AVERAGE_of_InDiscardsMonth,#Nodes_table.MAX_of_InDiscardsMonth   
      from
              #Nodes_table, Interfaces
      where
              Interfaces.interfaceid = #Nodes_table.InterfaceID
      order by
              NodeID, InterfaceID

      drop table #Nodes_table

        • Re: Custom Report Writer - SQL Query Not Working - Interface Discards
          njoylif

          The report writer tool can not do temporary tables, variables, etc.. IF I remember correctly. 
          Have your DBA create the query as a stored procedure and have report writer call that SP.

          EXEC RH_sp_FirewallFailoverStatus

          I definitely have a custom SQL report calling a StoredProcedure.

            • Re: Custom Report Writer - SQL Query Not Working - Interface Discards
              jbiggley

              Just waiting on the DBAs to configure this for me.  (I should really learn how to do this myself!)  Either way, I'll let you know what I find (and post both the stored procedure and SQL query for FYI)

               

              Thanks for the heads up.

              • Re: Custom Report Writer - SQL Query Not Working - Interface Discards
                yayyub

                Trying to create an advanced SQL report in report writer and thought it was limited to not being able to run T-SQL.  So, I found this post about using a stored proc and did that.  Below is the definition of my SP.  It runs great in SSMS.  However, when I configure the new report and execute it from report writer, it just spins...Any ideas as to what can be happening?  The SP does use a temp table as you can see in the definition.  Thanks!

                 

                screenshot1.jpg

                 

                 

                 

                CREATE PROCEDURE dbo.SAMComponentUpDownAvailability

                  @startdate int,

                  @enddate   int

                AS

                BEGIN

                 

                 

                create table #CompStatus (

                nodename varchar(100),

                applicationname varchar(200),

                componentname varchar(200),

                uptime int,

                downtime int,

                percentavailable decimal(5,2))

                 

                 

                insert into #compstatus (nodename,applicationname,componentname,uptime)

                select

                  n.caption,

                  ard.ApplicationName,

                  ard.ComponentName,

                  COUNT(*) * 5

                from apm_componentavailability ca, APM_AlertsAndReportsData ard, nodes n

                where ca.componentid = ard.componentid

                  and ard.nodeid = n.nodeid

                  and ca.componentstatus = 'UP'

                  and (ca.DateTime BETWEEN @startdate AND @enddate )

                -- and (ca.DateTime BETWEEN 42289 AND 42290 )

                  and ard.ApplicationName in ('NXIFS-EXT','NXPERVASIVE01','Nxstage AGIL Servers','Nxstage IFS Servers')

                group by n.caption, ard.ApplicationName, ard.ComponentName

                order by n.caption, ard.ApplicationName, ard.ComponentName

                 

                 

                --select * from #compstatus

                 

                 

                declare @nodeappcomp nvarchar(1000)

                declare @getnodeappcomp cursor

                 

                 

                set @getnodeappcomp = cursor for

                select nodename+applicationname+componentname from #compstatus

                 

                 

                open @getnodeappcomp

                fetch next from @getnodeappcomp into @nodeappcomp

                while @@fetch_status = 0

                begin

                update #compstatus set downtime =

                (select count(*)*5

                from apm_componentavailability ca, APM_AlertsAndReportsData ard, nodes n

                where n.caption+ard.applicationname+ard.ComponentName = @nodeappcomp

                  and ca.componentstatus = 'DOWN'

                  and (ca.DateTime BETWEEN @startdate AND @enddate )

                -- and (ca.DateTime BETWEEN 42289 AND 42290 )

                )

                where nodename+applicationname+componentname = @nodeappcomp

                FETCH NEXT from @getnodeappcomp into @nodeappcomp

                END

                CLOSE @getnodeappcomp

                DEALLOCATE @getnodeappcomp

                 

                 

                update #compstatus

                set percentavailable = uptime*100/(uptime+downtime)

                 

                 

                select * from #compstatus

                 

                 

                  drop table #compstatus

                END

                GO