Row Handle Invalid

Just asking the same question in another group, apologies if you've seen
this elsewhere.
What exactly does the above (subject) error mean? I'm getting it from an
adp file when used by a few people at the same time (each user has the
file in their own filespace though). Access is through windows
authentication and it only seemed to occur during an update of a
specific table.. The problem is it didn't happen to everyone and I
can't recreate it at all on my own, so am wondering if it was something
to do with the level of traffic to/from the server at that specific time.
Any ideas?
Cheers!
Chris
Could be caused by a problem with the MDAC components on this machine.
Otherwise, check this article.
276375 Error Message with Distributed Queries When Using ADO
http://support.microsoft.com/?id=276375
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Row Handle Invalid

Just asking the same question in another group, apologies if you've seen
this elsewhere.
What exactly does the above (subject) error mean? I'm getting it from an
adp file when used by a few people at the same time (each user has the
file in their own filespace though). Access is through windows
authentication and it only seemed to occur during an update of a
specific table.. The problem is it didn't happen to everyone and I
can't recreate it at all on my own, so am wondering if it was something
to do with the level of traffic to/from the server at that specific time.
Any ideas?
Cheers!
ChrisCould be caused by a problem with the MDAC components on this machine.
Otherwise, check this article.
276375 Error Message with Distributed Queries When Using ADO
http://support.microsoft.com/?id=276375
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

rounding datetime nnn to whole seconds

First of all my apologies if this has been covered elsewhere, I have been unable to find it despite a good few hours searching, okay here we go:

I have an mssql database with a table storing a datetime as >
2005-10-06 16:04:04.933

I have data in an access table that stores the same time as>
2005-10-06 16:04:05

i.e. it has rounded up the time. (under .500 gets rounded down)
I need to perform the same rounding on the mssql table (using an update)
I can't use cast as smalldatetime is too small, timestamp isn't right either.

I've tried convert: but this doesn't achieve rounding correctly (it only rounds down)

I would assume there is a function to accomplish my task . . . if only I could find it.

If any-one could help I would be most greatfull.

With thanks
Michael

I do not know of any built in way of doing this, but you could create your own function to accomplish this.

declare @.d as datetime
Set @.d = GetDate()
--Set @.d = '2005-10-06 16:04:04.933'
--Set @.d = '2005-10-06 16:04:04.433'
declare @.charDate as varchar(10)
Set @.charDate = Convert( varchar, @.d, 101)
declare @.Hour as int
declare @.Minute as int
declare @.Second as int
declare @.MiliSecond as int
Set @.Hour = DatePart(hh, @.d)
Set @.minute = DatePart(mi, @.d)
Set @.Second = DatePart(ss, @.d)
Set @.MiliSecond = DatePart(ms, @.d)
declare @.roundedDate as datetime
Set @.roundedDate = Convert( datetime, @.charDate, 101)
Set @.roundedDate = DateAdd( hh, @.Hour, @.roundedDate)
Set @.roundedDate = DateAdd( mi, @.Minute, @.roundedDate)
Set @.roundedDate = DateAdd( ss, @.Second, @.roundedDate)
if( @.MiliSecond > 500 )
Set @.roundedDate = DateAdd( ss, 1, @.roundedDate)
Select @.roundedDate

|||

You can achieve this by using DATEPART and DATEADD functions.

declare @.v datetime
set @.v = '2005-11-18 12:33:02.231'

select case when datepart(ms, @.v) >= 500 then dateadd(ms, 1000-datepart(ms, @.v), @.v) else dateadd(ms, -datepart(ms, @.v), @.v) end

|||

Michael,

I think this inline expression may be a bit more efficient:

dateadd(ms,500-datepart(ms,@.v + '00:00:00.500'),@.v)

Steve Kass

Drew University

kudosdude@.discussions.microsoft.com wrote:

> First of all my apologies if this has been covered elsewhere, I have

> been unable to find it despite a good few hours searching, okay here we

> go:

>

> I have an mssql database with a table storing a datetime as >

> 2005-10-06 16:04:04.933

>

> I have data in an access table that stores the same time as>

> 2005-10-06 16:04:05

>

> i.e. it has rounded up the time. (under .500 gets rounded down)

> I need to perform the same rounding on the mssql table (using an update)

> I can't use cast as smalldatetime is too small, timestamp isn't right

> either.

>

> I've tried convert: but this doesn't achieve rounding correctly (it only

> rounds down)

>

> I would assume there is a function to accomplish my task . . . if only I

> could find it.

>

> If any-one could help I would be most greatfull.

>

> With thanks

> Michael

>

|||Firstly thanks for all the responses, I didn't think there was a function to do it.
I would probably have done it similar to your way billrob & junn, but I have to say Steve Kass is a particurly elegant solution
Michael