InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 13:34:02 GMT20http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxYou are right. try to run the sp2 upgrade. This post answers your question.[url=http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic23583.aspx][/url]update.Best,BalaFri, 03 Dec 2010 17:43:43 GMTTalkSqlhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxThanks, Bala. The Error that I'm getting is as follows:Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27'object_schema_name' is not a recognized built-in function name.Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42Incorrect syntax near the keyword 'as'.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission.The first error seems to infer that I don't have SP2 installed... which I do have installed. Do you think that reapplying SP2 might fix this issue?Fri, 03 Dec 2010 17:17:02 GMTmedwards-789575http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxThen, there shouldn't be any changes to the code. Those post of mine are only for SQL Server 2008 I don't have SQL Server 2005 to test the code. Sorry my posts weren't not in much help to you. btw, what's the error you get ?Good Luck,Bala.Fri, 03 Dec 2010 17:13:25 GMTTalkSqlhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxBala, I'm sorry, I forgot to mention that I'm running 2005 SP2.Fri, 03 Dec 2010 16:50:56 GMTmedwards-789575http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxOK, I am not sure about your error, but assuming that this might be error you are getting..Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6Invalid column name 'cpu_ticks_in_ms'.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'usp_Main_GetCPUHistory', because it does not exist or you do not have permission.So, try this post which asks us to change the directory for the "Performance Dashboard "[Download and install the performance dashboard reports as normal (but save the files in the Program Files\Microsoft SQL Server\100\Tools\PerformanceDashboard directory) and then modify the setup.sql file as shown below before running it against your SQL Server 2008 instance.][url=http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx]http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx[/url]Once you are done with this, then come back check this post.Then also again go back to this post where you have to change a SP "MS_PerfDashboard.usp_Main_GetCPUHistory" If you are using SQL Server 2008. [url=http://sqlblogcasts.com/blogs/neilhambly/archive/2010/08/27/sql-2005-reports-using-the-dmv-dm-os-sys-info-tweaks-for-2008.aspx][/url]This is how i made it work, Hope this will work for you too. Let me know.BEST,BalaFri, 03 Dec 2010 16:44:44 GMTTalkSqlhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI did run it against the MSDB just to be sure and it gave the same error.Bala, I looked at the link you posted and it did not help.Fri, 03 Dec 2010 16:20:43 GMTmedwards-789575http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxMatt,I had the same error too, after spending few mins in Google I found this article which solved the issue. Hope this helps.http://sqlblogcasts.com/blogs/neilhambly/archive/2010/08/27/sql-2005-reports-using-the-dmv-dm-os-sys-info-tweaks-for-2008.aspxLet me know if this solved your problemBest,Bala.Fri, 03 Dec 2010 14:46:50 GMTTalkSqlhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxAre you running the script on the MSDB in a SQL 2005 instance that is SP2 or higher?Fri, 03 Dec 2010 13:17:06 GMTLaura Grobhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxAfter running the SETUP.SQL script, I'm getting the following errors:Msg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27'object_schema_name' is not a recognized built-in function name.Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42Incorrect syntax near the keyword 'as'.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission.Any help would be spiffy.Fri, 03 Dec 2010 12:55:52 GMTmedwards-789575http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxAnyone know if there are performance dashboard reports available for SQL 2008 and SQL 2008R2?Fri, 03 Dec 2010 12:49:37 GMTLaura Grobhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxGreat Article!I'd love to install the reports on a management server and have it email me the results from each of our servers, haven't quite started looking at how to programatically change the connection for each subscription. This way I coould have 1 set of RDL's that hit a different connection for each subscription.Anyone have any thoughts?Fri, 03 Dec 2010 11:02:42 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxGood one!!Fri, 03 Dec 2010 06:11:25 GMTBoxerhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxHi,Nice article.You can discover a lot more about DMVs in this forthcoming book www.manning.com/stirk. Chapter 1 can be downloaded for free and includes scripts for:A simple monitorFinding your slowest queriesFind your missing indexesIdentifying what SQL is running nowQuickly find a cached planThanksIan Fri, 03 Dec 2010 00:51:44 GMTianstirkhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxNice read Robert. But I still find SSRS incometent to exploit the benefits of enterprise dashboarding.Fri, 15 Oct 2010 05:14:00 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxWhat changed is the number of days the system was up and that is what caused the issue.Thu, 17 Jun 2010 17:47:51 GMTLaura Grobhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspx[quote][b]sibir1us (7/28/2009)[/b][hr]If you would like to use the Performance Dashboard 2005 on SQL Server 2008 you will have to make the following change in the usp_Main_GetSessionInfo stored procedure. Change the line:sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,tosum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,[/quote]I ran into to this problem, "Difference of two datetime columns caused overflow at runtime" on sql2005 today for the first time. Your code above worked....Thanks! I just wonder what changed to cause it to start happening....????Thu, 17 Jun 2010 06:26:48 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxThe interesting part is it works for all the other sub reports - this is the only one that is having a problem.Thu, 13 May 2010 10:47:41 GMTLaura Grobhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxThe dataset would also need the server name you are reporting on. That's an interesting modification. I'm not sure you'll be able to make both the report and the data source dynamic.Thu, 13 May 2010 06:31:08 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspx[quote][b]Laura Grob (5/12/2010)[/b][hr]I had a similar issue when trying to use windows integrated security. When I used credentials supplied by the user and checked the box for use as windows credentials when connecting to the data source I was able to run the reports. The user does have to enter their full domain\lanid and password but, it worked for me as long as there was a trust between the domains.[/quote]Sorry this was in response to SSC Veteran's questionWed, 12 May 2010 16:44:58 GMTLaura Grobhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI had a similar issue when trying to use windows integrated security. When I used credentials supplied by the user and checked the box for use as windows credentials when connecting to the data source I was able to run the reports. The user does have to enter their full domain\lanid and password but, it worked for me as long as there was a trust between the domains.Wed, 12 May 2010 16:43:45 GMTLaura Grobhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxMost of it works great published in SSRS and modified to allow the server name and instance name to manually entered except when I go into a sub, sub report. For example if I run the main performance dashboard report, then click on expensive queries by duration all is well. When I click on one of the queries returned by that sub report I get a screen that shows all of the prompts but does not return data. I went into BIDS and checked what the blank parameter was and found it was servername. If I fill that in I still receive an error message regarding a dataset. Any suggestions?Wed, 12 May 2010 16:32:05 GMTLaura Grobhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxThank you for the great article. It works for me if I ask it to prompt for login and I give my credentials. But if I use the service account that has sysadmin privileges I get an error like this:An error has occurred during report processing.Cannot impersonate user for data source 'DataSource1'.Logon failed.Logon failure: the user has not been granted the requested logon type at this computer. (Exception from HRESULT: 0x80070569) Also if I use my credentials the authentication is not passed down to the smaller reports - it only works on main performance dashboard. Any suggestions much appreciated, thanks.Tue, 25 Aug 2009 14:06:06 GMTdma-669038http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxNo luck still getting the same error..Tue, 04 Aug 2009 15:17:52 GMTSam-633226http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxTry the steps I listed in my posts.Tue, 04 Aug 2009 06:18:20 GMTRome-828069http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxWhile running the script ,me getting this error...need helpMsg 195, Level 15, State 10, Procedure usp_GetPageDetails, Line 27'object_schema_name' is not a recognized built-in function name.Msg 156, Level 15, State 1, Procedure usp_GetPageDetails, Line 42Incorrect syntax near the keyword 'as'.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'usp_GetPageDetails', because it does not exist or you do not have permission.Thanks in advance...SamMon, 03 Aug 2009 15:23:22 GMTSam-633226http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspx[quote][b]Bob Griffin (7/28/2009)[/b][hr][quote][b]nathan.holtrop (7/28/2009)[/b][hr]I am having the same problem as mcasner, any suggestions?[/quote]For the permissions problem, make sure you have select privilege on view server state...here's some info on that:from: [url]http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx [/url]To query a server scoped DMV, the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV, the user must have SELECT privilege on VIEW DATABASE STATE.GRANT VIEW SERVER STATE to <Login> GRANT VIEW DATABASE STATE to <User> [/quote]Just so others know...GRANT VIEW SERVER STATE to <Login> GRANT VIEW DATABASE STATE to <User> For these commands to work the user had to exist in the msdb db and the master db.As well as grant alter trace to <user>Wed, 29 Jul 2009 07:13:47 GMTRome-828069http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI also had an issue with usp_Main_GetSessionInfo due to the max value for milliseconds in the DateDiff function being less than 24 Days (etc). Therefore, to maintain a good degree of accuracy still (and having had the problem before), I rewrote it like so :-USE [msdb]GOALTER PROCEDURE [MS_PerfDashboard].[usp_Main_GetSessionInfo]ASBEGIN SELECT COUNT(*) AS num_sessions, SUM(s.total_elapsed_time) AS total_elapsed_time, SUM(s.cpu_time) AS cpu_time, SUM(s.total_elapsed_time) - SUM(s.cpu_time) AS wait_time, SUM(s.login_time) - SUM(s.total_elapsed_time) AS idle_connection_time, cache_hit_ratio=(CASE WHEN SUM(s.logical_reads) > 0 THEN (SUM(s.logical_reads) - ISNULL(SUM(s.reads),0)) / CONVERT(FLOAT,SUM(s.logical_reads)) ELSE NULL END) FROM (SELECT session_id, CONVERT(BIGINT,total_elapsed_time) AS total_elapsed_time, CONVERT(BIGINT,cpu_time) AS cpu_time, CONVERT(BIGINT,DATEDIFF(ss,login_time,GETDATE()))*1000 AS login_time, logical_reads, reads FROM sys.dm_exec_sessions WHERE is_user_process = 0x1 AND login_time<DATEADD(minute,-35791,GETDATE()) UNION SELECT session_id, CONVERT(BIGINT,total_elapsed_time) AS total_elapsed_time, CONVERT(BIGINT,cpu_time) AS cpu_time, CONVERT(BIGINT,DATEDIFF(ms,login_time,GETDATE())) AS login_time, logical_reads,reads FROM sys.dm_exec_sessions WHERE is_user_process = 0x1 AND login_time>DATEADD(minute,-35791,GETDATE())) sENDWed, 29 Jul 2009 05:53:29 GMTMike Bennett-683749http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspx[quote][b]amc (7/28/2009)[/b][hr]Thanks Bob! That was quick.I re-traced my steps and as you just stated that's where I went wrong. So I'm back on track now. However, When I opened the "performance_dashboard_main.rd.",I received this error in the Preview tab:An error has occurred.....Cannot read the next data row for the data set SESSION_CPU_WAIT_INFO.Difference of two datetime colums caused overflow at runtime. Please advise. [/quote]Check out the first post of this discussion by sibir1us. I think that is the solution to what you are seeing. Tue, 28 Jul 2009 18:02:01 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspx[quote][b]comfortzone0 (7/28/2009)[/b][hr]Hi, I ran into this error:Could not find stored procedure msdb.MS_PerfDashboard.usp_CheckDependenciesAny suggestions?Thank you.[/quote]Did you get any errors when you ran the sql script file...?Tue, 28 Jul 2009 17:58:52 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspx[quote][b]nathan.holtrop (7/28/2009)[/b][hr]I am having the same problem as mcasner, any suggestions?[/quote]For the permissions problem, make sure you have select privilege on view server state...here's some info on that:from: [url]http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx [/url]To query a server scoped DMV, the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV, the user must have SELECT privilege on VIEW DATABASE STATE.GRANT VIEW SERVER STATE to <Login> GRANT VIEW DATABASE STATE to <User> Tue, 28 Jul 2009 17:56:28 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI actually found a better solution to my issue (converting dashboard to 2008)http://blogs.technet.com/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspxTue, 28 Jul 2009 14:29:25 GMTMattW2010http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxHi, I ran into this error:Could not find stored procedure msdb.MS_PerfDashboard.usp_CheckDependenciesAny suggestions?Thank you.Tue, 28 Jul 2009 14:25:19 GMTcomfortzone0http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI think I found the answer:http://blogs.msdn.com/vascov/archive/2008/09/30/using-performance-dashboard-with-sql-server-2008.aspxcreate procedure MS_PerfDashboard.usp_Main_GetCPUHistoryasbegin declare @ts_now bigint select @ts_now = ms_ticks from sys.dm_os_sys_infoTue, 28 Jul 2009 13:58:39 GMTMattW2010http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI am running SQL Server 2005 and I'm still getting the error I mentioned.Tue, 28 Jul 2009 13:45:46 GMTmcasnerhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxThe Performance Dashboard was written for sql 2005. I have not ran on sql 2008, so I will have to defer the rest of the group...Tue, 28 Jul 2009 13:42:56 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxFor 2008, should we use ms_ticks instead of cpu_ticks_in_ms from sys.dm_os_sys_info when creating procedure MS_PerfDashboard.usp_Main_GetCPUHistory?Tue, 28 Jul 2009 12:56:49 GMTMattW2010http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI am having the same problem as mcasner, any suggestions?Tue, 28 Jul 2009 12:08:07 GMTRome-828069http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxThanks Bob! That was quick.I re-traced my steps and as you just stated that's where I went wrong. So I'm back on track now. However, When I opened the "performance_dashboard_main.rd.",I received this error in the Preview tab:An error has occurred.....Cannot read the next data row for the data set SESSION_CPU_WAIT_INFO.Difference of two datetime colums caused overflow at runtime. Please advise. Tue, 28 Jul 2009 11:53:03 GMTamc-885860http://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxAMC,Just make sure your created it as a "report server project" on the opening "create project" screen and the folder should be there.Tue, 28 Jul 2009 11:39:52 GMTBob Griffinhttp://www.sqlservercentral.com/Forums/Topic760534-1510-1.aspxI followed the steps in this post and everything seems to work. However, I get an error "query execution failed for data set "CPU_UTILIZATION_HISTORY" User does not have permission to perform this action.I thought that the grant statements in the setup.sql would have taken care of this.Anyone have any suggestions?Tue, 28 Jul 2009 11:39:41 GMTmcasner