February 26, 2010 at 4:19 am
Hi there
I've developed a report who's dataset is from a stored procedure which has three parameters: server name, start date, and end date.
The dataset designer works as expected when I manually input the three parameters:
http://i50.tinypic.com/6ntik8.jpg
My report layout is:
http://i47.tinypic.com/27wsnth.jpg
But when I preview the report, nothing is displayed:
http://i45.tinypic.com/2mqlwdc.jpg
The stored proc is:
CREATE PROCEDURE [dbo].[UspRepCpu]
@ServerName VARCHAR(100),
@StartDateCHAR(10),
@EndDateCHAR(10)
AS
DECLARE
@Query VARCHAR(4000)
Set @ServerName = @ServerName + 'PerfMonData'
SET @Query =
'
WITH CpuUse ([Server], [Counter], [Date], [Time], [Val]) AS
(
SELECT
Servers.ServerName AS [Server] ,
Counters.CounterName AS [Counter] ,
convert (varchar(16),DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ),103 ) AS StartDate ,
right(Ltrim(rtrim(convert(nvarchar(16), DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ), 121))),5) AS StartTime,
ROUND ( AVG ( ' + @ServerName +'.CounterValue ) , 0 )AS CounterValue
FROM
Counters
INNER JOIN ' + @ServerName +'
ON Counters.Id = ' + @ServerName +'.CounterId
INNER JOIN Servers
ON ' + @ServerName +'.ServerId = Servers.Id
AND
(Counters.CounterName LIKE ''%Processor%'')
GROUP BY
Servers.ServerName ,
Counters.CounterName ,
DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 )
UNION ALL
SELECT
Servers.ServerName AS [Server] ,
Counters.CounterName AS [Counter] ,
convert (varchar(16),DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ),103 ) AS StartDate ,
right(Ltrim(rtrim(convert(nvarchar(16), DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ), 121))),5) AS StartTime,
ROUND ( AVG ( PerfMonData.CounterValue ) , 0 )AS CounterValue
FROM
Counters
INNER JOIN PerfMonData
ON Counters.Id = PerfMonData.CounterId
INNER JOIN Servers
ON PerfMonData.ServerId = Servers.Id
WHERE Servers.ServerName = REPLACE(''' + @ServerName +''',''PerfMonData'','''')
AND
(Counters.CounterName LIKE ''%Processor%'')
GROUP BY
Servers.ServerName ,
Counters.CounterName ,
DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 )
)
SELECT * FROM CpuUse
WHERE [Date] Between ''' + @StartDate + ''' AND ''' + @EndDate + '''
ORDER BY [Date] DESC, [Time] DESC, [Counter] ASC
'
exec (@Query)
February 28, 2010 at 10:13 am
Hi,
I m not sure ,i noticed u r using startdate and enddate in sp as 'Char' but in report ur using 'Date' datatype chage u r Datatype in Sp as date time hopefully it will work....
Thanks
Veeren
Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉
March 1, 2010 at 3:53 am
Yeah you were right. Not long after I made the post I changed the stored procedure to:
ALTER PROCEDURE [dbo].[UspRepCpu]
@ServerName VARCHAR(100),
@StartDateDATETIME,
@EndDateDATETIME
AS
DECLARE
@Query VARCHAR(4000)
Set @ServerName = @ServerName + 'PerfMonData'
SET @Query =
'
SELECT
Servers.ServerName AS [Server] ,
Counters.CounterName AS [Counter] ,
convert (char(10),DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ),103 ) + '' '' + convert(CHAR(8), DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 ), 114) AS StartDate ,
ROUND ( AVG ( ' + @ServerName +'.CounterValue ) , 0 )AS CounterValue
FROM
Counters
INNER JOIN ' + @ServerName +'
ON Counters.Id = ' + @ServerName +'.CounterId
INNER JOIN Servers
ON ' + @ServerName +'.ServerId = Servers.Id
AND
(Counters.CounterName LIKE ''%Processor%'')
GROUP BY
Servers.ServerName ,
Counters.CounterName ,
DATEADD ( hh , DATEDIFF ( hh , 0 , ' + @ServerName +'.CounterDateTime ) , 0 )
UNION ALL
SELECT
Servers.ServerName AS [Server] ,
Counters.CounterName AS [Counter] ,
convert (char(10),DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ),103 ) + '' '' + convert(CHAR(8), DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 ), 114) AS StartDate,
ROUND ( AVG ( PerfMonData.CounterValue ) , 0 )AS CounterValue
FROM
Counters
INNER JOIN PerfMonData
ON Counters.Id = PerfMonData.CounterId
INNER JOIN Servers
ON PerfMonData.ServerId = Servers.Id
WHERE Servers.ServerName = REPLACE(''' + @ServerName +''',''PerfMonData'','''')
AND
(Counters.CounterName LIKE ''%Processor%'')
GROUP BY
Servers.ServerName ,
Counters.CounterName ,
DATEADD ( hh , DATEDIFF ( hh , 0 , PerfMonData.CounterDateTime ) , 0 )
'
EXEC (@Query)
March 1, 2010 at 11:06 pm
Hi ,
Great please let us know for u r questions keep on post ur querys in SSC.
Thanks
Veeren:-)
Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply