Performance Dashboard 2012,SQL Server 2008 and a non-working Recent CPU report…

Hi all,

today I’ve spent little time installing the new SQL Server 2012 Performance Dashboard (you can find it here).  Then I picked a test server (still SQL Server 2008 R2, build .2500) and ran the setup.sql to provide all the necessary procedures for the reports.

When I tried to open the „Recent_CPU“ Report by clicking on the blue bar

I got the error „A data source instance has not been supplied for the data source ‚CHECK_DEPENDENCIES‘. After a refresh of the report a 2nd error showed up: Error converting data type nvarchar(max) to datetime. My first thought was: „Hey, post a quick question at ask.sqlservercentral.com and wait for answers.“  Then I got ambitious and started SQL Profiler. Since it was only a quick trace I ran the GUI. Normally I would have created a server-side trace. You still don’t know what a server-side trace is? Take a look at Stairway to Server-side Tracing.

I catched a user error saying „Error converting data type nvarchar(max) to datetime.“ right after this statement:
EXEC sp_executesql @stmt=N'exec msdb.MS_PerfDashboard.usp_QueryStatsRecentActivity @WithActivitySince',@params=N'@WithActivitySince NVarChar(max)',@WithActivitySince=N'27.07.2012 19:52:17'

That was an interesting information. So I opened the recent_cpu.rdl in BIDS2008 and checked the Parameters and guess what? The parameter ‚WithActivitySince‘ was declared as string. Since I already made my experience messing up reports with BIDS 2008 when the origin report was created with the 2005 XML schema I just opened the rdl file with notepad++ and changed the defined data type  from „String“

<ReportParameter Name="WithActivitySince">

 <DataType>String</DataType>

  <AllowBlank>true</AllowBlank>

  <Prompt>WithActivitySince</Prompt>

</ReportParameter>

to „DateTime“

<ReportParameter Name="WithActivitySince">

 <DataType>DateTime</DataType>

  <AllowBlank>true</AllowBlank>

  <Prompt>WithActivitySince</Prompt>

</ReportParameter>

After this little change I was able to run the recent CPU report without any problems and double-checked that the report still worked on a SQL 2012 as well.

Regards and have a great weekend

Advertisements

Über Dirk Hondong

A MS server and ms sql server admin guy from germany. want to improve my skills a little bit, sharing my daily experience
Dieser Beitrag wurde unter Maintenance, Performance Dashboard, SQL Server Administration abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

3 Antworten zu Performance Dashboard 2012,SQL Server 2008 and a non-working Recent CPU report…

  1. wendell sa schreibt:

    Thanks!

  2. Goran schreibt:

    Thanks!
    Great job!

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s