4 Replies Latest reply on Jan 25, 2012 2:56 PM by satish.lx

    INSERT data via  report Writer tool

    satish.lx

      I have very complex query and which required to create some temp tables using store procedure. when i am using Report Writer Advance SQL to write query its working but at data INSERT time its just showing me message "Query Database..." until unless i cancel that message it will be there.

      Everything seems works but only this message is annoying. here is my sample code.

       

      INSERT INTO #tempTable (Studies, Availability)
      EXEC usp_study_acr

      SELECT *
      FROM #tempTable

       

      Following is message i am getting. It would be there until i cancel it

        • Re: INSERT data via  report Writer tool
          sean.martinez

          Report Writer is designed to only retrieve information, not to input information, which is why the Query Database will always run as it is expecting results. You will want to use Database Manager or SQL Studio Manager for any SQL Statements that are not Select Statements.

            • Re: INSERT data via  report Writer tool
              satish.lx

              You mean say we can do store procedure with report writer ?

              I don't think i can run Query on real time via SQL Studio Manager. Because my querying doing some stuff on fly and generating report. How could i use SQL Studio Manager to do same kind of work?

                • Re: INSERT data via  report Writer tool
                  sean.martinez

                  I found one instance similar to what you are doing in Report Writer. The 95th percentile Reports that come with Orion do have Declares, sets, create temp table, an Inert and a Select Statement and they all execute without issue. Here is the report for you to base your report on:

                   

                   

                   

                  DECLARE @StartDate DateTime

                  DECLARE @EndDate DateTime

                   

                  SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)

                  SET @EndDate = GetDate()

                  set nocount on

                  create table #tmpJoin (fromDate datetime, toDate datetime)

                   

                  insert into #tmpJoin values(@StartDate,@EndDate)

                  set nocount off

                   

                  SELECT Interfaces.InterfaceId,

                  Nodes.NodeID,

                  Nodes.Caption AS NodeName,

                  Nodes.VendorIcon AS Vendor_Icon,       

                  Interfaces.Caption AS Interface_Caption,

                  Interfaces.InterfaceIcon AS Interface_Icon,

                  Maxbps_In95,

                  Maxbps_Out95,

                  Maxbps_95

                  FROM Nodes 

                  INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)

                  INNER JOIN

                  (SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95

                  FROM (SELECT DISTINCT A.InterfaceID

                  FROM dbo.InterfaceTraffic A JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate

                  ) AS AA ) as RESULT_IN ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)

                  INNER JOIN

                  (SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95

                  FROM (SELECT DISTINCT A.InterfaceID

                  FROM dbo.InterfaceTraffic A

                  JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate

                  ) AS AA

                  ) as RESULT_OUT ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)

                  INNER JOIN(SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95

                  FROM (SELECT DISTINCT A.InterfaceID FROM dbo.InterfaceTraffic A

                  JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate

                  ) AS AA

                  ) as RESULT_MAX ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)

                   ORDER BY NodeName, Interface_Caption

                  Drop table #tmpJoin