Arithmetic overflow error converting expression to data type datetime

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

Are we not able to edit stored procedures in SQL express?

Have sql 2005 express installed.

Running a database for a DotNet Nuke site

Opened Microsoft SQL Server Management Studio Express.

Navigated to CASPORTAL\Databases\DotNetNuke\Programmability\Stored Procedures\dbo.AddUser

Right clicked dbo.AddUser and selected "modify"

This allowed me to paste the additional code into the right hand window/pane, however when I try and save this it wants to save it a seperate file /query. Is there something I don't understand?

am I not able to edit the original stored procedure?

Hi,

thats a common minsunderstanding. Hitting the disc symbol will save the data, what you will have to do is to execute the stored procedure within this pane. There is a button for this reading "Execute", this will execute the current query presented in your pane (which is an ALTER PROCEDURE statement). This will apply your changes to the database.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Are the databases created within VWD Express accessible by aspnet_regsql.exe or Management

Hi,

When I attempt to add the membership functionality to a database created within VWD Express, by using the aspnet_regsql.exe application (connecting to ./SQLEXPRESS using Windows authentication), the database I've created within VWD Express doesn't appear within the list of accessible databases. I have the same problem when attempting to access the same database within Management Studio Express.

Does this mean that databases created from within VWD Express don't use the same storage mechanism or paradigm as those already available within Management Studio Express? Or perhaps I need to somehow register the database using Management Studio Express? As a side note, I'm not sure if the "File/Open File" menu option within Management Studio Express holds the answer to my problems, but rather bizarrely, it crashes Management Studio Express when I select it.

Apologies for the rambling - in essence my question is "How can I make databases created within VWD Express appear within aspnet_regsql.exe and/or Management Studio Express?"

Many thanks,

Jon

Hey,

You can attach them. However, I had problems with any database in my documents showing up. But attaching is the process that makes it work. So you will want to attach database from the list of options, and then navigate to the database.

|||

http://forums.asp.net/thread/1456464.aspx

Step 1 to 6 will do what the previous poster said :)

|||

Thanks for your help folks.

So the only problem I have remaining is that (as stated in the referenced post) Management Studio can't see the database in order to attach it unless I move it out of my home directory (presumably a file permissions problem). Ideally I need both VWD and Management Studio to be able to access the database without moving it between two different locations. Which leaves me with two options:

1) Change the permissions on my home directory such that Management Studio can see the MDB/LDB and therefore attach it. However, I'm not sure what permissions I'd need to change.

2) Move the MDB and LDB out of my home directory and to a place where Management Studio can see them (e.g. the standard database directory for SQL Server 2005 Express) and update the path of the database within VWD. However, VWD doesn't appear to allow me to change the path of the database within the database properties in Solution Explorer.

Can anybody advise on how to do either of the above, and/or which approach is preferable?

Many thanks.

|||If you want both to be able to see it, your best bet is to put the database files in the SQL Server directory (where all the other databases are), attach it, and then change your connection string in your project to access the database by IP address instead.

The MDF-in-APP_DATA approach is when you're going to use VWD exclusively to deal with it. Otherwise, go the "good old fashionned way", which is to let SQL Server handle the database fully.|||

Yep, that's what I ended up doing anyway - thanks for the confirmation.

Not sure why they don't just put the MDB/LDB in the SQL Server data directory in the first place...

|||Because if you only use VWD, and your host supports sql server 2005 with attach, you can do XCOPY deployement, while if they put the mdf in the sql server directory, you need to export the database :)