The following sql report runs just fine without the bold code, but as soon as it's added in the report doesn't load and then all other Orion reports will not load until the server is reset.
Any idea's why this might be occurring?
<Report Version="1.0" Group="DEV" Title="95 tile test" ModuleTitle="" Type="SQL" TypeDescription="" Icon="SQL" Schema="" SubTitle="" Description="" Footer="" Time_Frame="Named" Named_Time_Frame="Last 30 Days" Relative_Time_Frame="24 Hours" Starting_DateTime="7/30/2011 0:0:0" Ending_DateTime="8/30/2011 0:0:0" Grouping="Date" Group_Position="Beginning" SQL="DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
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.HA,
Nodes.Site,
Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Interfaces.InterfaceIcon AS Interface_Icon,
WANBandwidth AS Bandwidth,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95,
SUM(InterfaceTraffic.In_TotalBytes) AS SUM_of_Total_Bytes_Received
FROM Nodes
INNER JOIN InterfaceTraffic ON (InterfaceTraffic.NodeID = Nodes.NodeID)
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)
WHERE interfaces.WANFeed = 'Primary' AND nodes.region <> 'ENG'
GROUP BY Nodes.HA, Nodes.NodeID, Nodes.Site, Nodes.VendorIcon, Nodes.Caption, Interfaces.InterfaceIcon, Interfaces.InterfaceID, Nodes.wanbandwidth, RESULT_IN.Maxbps_in95, RESULT_OUT.maxbps_out95, RESULT_MAX.maxbps_95
ORDER BY WANBandwidth DESC, MAXbps_95 DESC
Drop table #tmpJoin" TopX="All" TopXCount="10" TopXPercent="10" Orientation="Landscape" CookedData="TRUE" Web="TRUE" GroupByDays="TRUE" ShowFolders=""><QueryBuilder><Fields></Fields><Filter></Filter></QueryBuilder><LayoutGroups></LayoutGroups><Fields></Fields></Report>
