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 ID 18456 SBS2003 with SQL2005

Hi @.all

We have a SBS2K3 with SQL2k5.

On every Startup we get the following Error:

Ereignistyp: Fehlerüberw.
Ereignisquelle: MSSQL$SHAREPOINT
Ereigniskategorie: (4)
Ereigniskennung: 18456
Datum: 17.10.2006
Zeit: 00:20:25
Benutzer: NT-AUTORIT?T\NETZWERKDIENST
Computer: PDC
Beschreibung:
Fehler bei der Anmeldung für den Benutzer 'NT-AUTORIT?T\NETZWERKDIENST'. [CLIENT: 192.168.2.250]

Weitere Informationen über die Hilfe- und Supportdienste erhalten Sie unter http://go.microsoft.com/fwlink/events.asp.
Daten:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 0f 00 00 00 50 00 44 00 ....P.D.
0010: 43 00 5c 00 53 00 48 00 C.\.S.H.
0018: 41 00 52 00 45 00 50 00 A.R.E.P.
0020: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0028: 00 00 07 00 00 00 6d 00 ......m.
0030: 61 00 73 00 74 00 65 00 a.s.t.e.
0038: 72 00 00 00 r...

The Client: 192.168.2.250 is the SBS itself with the internal LAN NIC (the SBS have 2 NIC′s for WAN and LAN)

The NT-Authority\Networkservice is in Logon of the DB and have dbcreate and dbsecurity rights.

What can we do?

regards.

Can you paste the error from the ERRORLOG file and translate the German into English? The error state will be very helpful in determining what is going on (see http://msdn2.microsoft.com/en-us/library/ms366351.aspx).

Thanks
Laurentiu