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.
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>
<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