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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy