Error in new SQL2K5 Performance Dashboard Reports

I tried this new SQL2K5 Performance Dashboard Reports using custom reports in Management Studio.

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

But running it first on any server gives me this error:

Difference of two datetime columns caused overflow at runtime.

Has anybody come across this error? How to fix it?

Thanks in advance!

- Rupesh

first check the sp2 is applied or not.

ref : http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

Because DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds it will work so basicalyy if you modify trhe offending line

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to

sum(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,

then it will work

hopes this helps the rest of you who have the same problem.

Madhu

|||

Hi,

I am facing an error saying ‘Arithmetic overflow error converting expression to data type datetime.’ In data base due to my following query.

Then I tried with cast and convert function too, still I got the error.

select*

from datetable

wherecast(('May 29 20076:30:00:000PM' - endtime) as int) >=2

andcast(('May 29 20076:30:00:000PM' - endtime)as int)<=3

anddatetable_id= 102

order by datetable_iddesc

I got this beacause of some bad ‘endtime’ data in datetable for datetable_id102 : 5465-08-12 12:00:00.000.

But I need to support all type of date here and the table is also huge. So I have this col as indexed.

I thought of to use datediff func here. again I am not sure what will be the performance impact on my query, coz it will diff and convert to int and compare for each of the row.

So can any body suggest how efficiently can I handle this?

Thanks

~Dhiru

Error in FormAuthentication

I'm using Microsoft Forms Authentication sample to implement custom security
extension. Everything works fine except the following:
(1) After i registerd the user ,i cannot log into the Report Manager.It is
saying like
"The request failed with HTTP status 401: Access Denied."
(2) But with the same username and password i am entering into the
Reportserver
I set the credentials in Reference.cs as
public ReportingService()
{
this.Credentials = new
System.Net.NetworkCredential("username","password","Domain");
this.Url = "http://localhost/reportserver/reportservice.asmx";
}
And the logfile saying like
CantCommunicateWithReportServerException: Unable to communicate with report
server. Please verify that the report server is operational.
at Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
methodname)
at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain()
aspnet_wp!ui!134!2/22/2005-11:18:01:: e ERROR: Exception in ShowErrorPage:
System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
could anybody tell me in which file possibly wrong?
Thanks,
simmiIf you are using Forms Authentication you have to log on using LogOnUser
(see my post to your other post).
--
Adrian M.
MCP
"simmi" <simmi@.discussions.microsoft.com> wrote in message
news:8DEC1F92-5E7B-445B-B07C-79A20F0A02BD@.microsoft.com...
> I'm using Microsoft Forms Authentication sample to implement custom
> security
> extension. Everything works fine except the following:
> (1) After i registerd the user ,i cannot log into the Report Manager.It is
> saying like
> "The request failed with HTTP status 401: Access Denied."
> (2) But with the same username and password i am entering into the
> Reportserver
> I set the credentials in Reference.cs as
> public ReportingService()
> {
> this.Credentials = new
> System.Net.NetworkCredential("username","password","Domain");
> this.Url = "http://localhost/reportserver/reportservice.asmx";
> }
>
> And the logfile saying like
> CantCommunicateWithReportServerException: Unable to communicate with
> report
> server. Please verify that the report server is operational.
> at Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetSecureMethods()
> at
> Microsoft.SqlServer.ReportingServices.RSConnection.IsSecureMethod(String
> methodname)
> at Microsoft.ReportingServices.UI.Global.SecureAllAPI()
> at
> Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel
> level)
> at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object
> sender, EventArgs args)
> at System.EventHandler.Invoke(Object sender, EventArgs e)
> at System.Web.UI.Control.OnInit(EventArgs e)
> at System.Web.UI.Control.InitRecursive(Control namingContainer)
> at System.Web.UI.Page.ProcessRequestMain()
> aspnet_wp!ui!134!2/22/2005-11:18:01:: e ERROR: Exception in ShowErrorPage:
> System.Threading.ThreadAbortException: Thread was being aborted.
> at System.Threading.Thread.AbortInternal()
> at System.Threading.Thread.Abort(Object stateInfo)
> at System.Web.HttpResponse.End()
> at System.Web.HttpServerUtility.Transfer(String path, Boolean
> preserveForm)
> at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
> errMsg)
> at at System.Threading.Thread.AbortInternal()
> at System.Threading.Thread.Abort(Object stateInfo)
> at System.Web.HttpResponse.End()
> at System.Web.HttpServerUtility.Transfer(String path, Boolean
> preserveForm)
> at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String
> errMsg)
>
> could anybody tell me in which file possibly wrong?
> Thanks,
> simmi
>

Error handling for Script Task

I'm importing an Excel file and massaging it with a custom script task. I get an error "Index was outside the bounds of the array." That error is helpful but I needed to know which of the 70,000+ lines the error occurred on. What is the best way of devbugging this issue? Try-Catch-Finally with messageboxes in the Catch clause?

Barkingdog

Assuming this is Script Task, not Script Dataflow Component, you can set a breakpoint at the beggining of the task, then use menu Debug > Exceptions, check the 'Thrown' box - this will make IDE stop when an exception is thrown by the Script Task.

P.S. 70,000+ lines seems to be way too much for script task. You may find a custom task to be a better option for such undertaking.