Persistence of Time... DateTime bugs, that is

Guess what? The DateTime rounding bug never got fixed in SQL Server
2005. You know, this one:
SELECT CAST('10/25/2005 23:59:59.990' AS DATETIME) AS [990]
SELECT CAST('10/25/2005 23:59:59.991' AS DATETIME) AS [991]
SELECT CAST('10/25/2005 23:59:59.992' AS DATETIME) AS [992]
SELECT CAST('10/25/2005 23:59:59.993' AS DATETIME) AS [993]
SELECT CAST('10/25/2005 23:59:59.994' AS DATETIME) AS [994]
SELECT CAST('10/25/2005 23:59:59.995' AS DATETIME) AS [995]
SELECT CAST('10/25/2005 23:59:59.996' AS DATETIME) AS [996]
SELECT CAST('10/25/2005 23:59:59.997' AS DATETIME) AS [997]
SELECT CAST('10/25/2005 23:59:59.998' AS DATETIME) AS [998]
SELECT CAST('10/25/2005 23:59:59.999' AS DATETIME) AS [999]
There's an issue for it in MSDN Feedback Center, which you can vote on:
http://lab.msdn.microsoft.com/produ...37-10b837e28bc3
At this point it probably won't happen until a service pack, but at
least if we all vote, maybe it will get noticed. I could have sworn
someone promised relief years ago when I first read about this problem,
but it appears that was a hallucination.
argh..
Well, back to my stupid date-trimmed unit tests. Good day,
-ChrisChris Durkin wrote:
> Guess what? The DateTime rounding bug never got fixed in SQL Server
> 2005. You know, this one:
> SELECT CAST('10/25/2005 23:59:59.990' AS DATETIME) AS [990]
> SELECT CAST('10/25/2005 23:59:59.991' AS DATETIME) AS [991]
> SELECT CAST('10/25/2005 23:59:59.992' AS DATETIME) AS [992]
> SELECT CAST('10/25/2005 23:59:59.993' AS DATETIME) AS [993]
> SELECT CAST('10/25/2005 23:59:59.994' AS DATETIME) AS [994]
> SELECT CAST('10/25/2005 23:59:59.995' AS DATETIME) AS [995]
> SELECT CAST('10/25/2005 23:59:59.996' AS DATETIME) AS [996]
> SELECT CAST('10/25/2005 23:59:59.997' AS DATETIME) AS [997]
> SELECT CAST('10/25/2005 23:59:59.998' AS DATETIME) AS [998]
> SELECT CAST('10/25/2005 23:59:59.999' AS DATETIME) AS [999]
> There's an issue for it in MSDN Feedback Center, which you can vote
> on:
>
http://lab.msdn.microsoft.com/produ...
7-10b837e28bc3
> At this point it probably won't happen until a service pack, but at
> least if we all vote, maybe it will get noticed. I could have sworn
> someone promised relief years ago when I first read about this
> problem, but it appears that was a hallucination.
>
Didn't you read the MS response on that page? It's been "Resolved as By
Design "
In other words, it's not a bug: it's the way it's supposed to work. No
matter how many votes it gets, it is not going to be "fixed".
This rounding has occurred since the SQL 6.5 days, at least. If more
resolution is needed, a different datatype needs to be used.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Not a bug. From Books Online:
"Date and time data from January 1, 1753 through December 31, 9999, to
an accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). Values are rounded to increments of
.000, .003, or .007 seconds, as shown in the table."
Exactly what enhancement are you looking for? Increasing the accuracy
from 0.00333 to 0.001 would seem to add very little value. Increasing
precision by 1 or 2 more decimals might be a more useful enhancement
but would cost extra strorage.
David Portas
SQL Server MVP
--|||<snip>

> This rounding has occurred since the SQL 6.5 days, at least. If more
> resolution is needed, a different datatype needs to be used.
What datatype are we supposed to use - is there a new datetime type in
2005 with more precision?|||I realize it's by design. You're being disingenuous. My point is that
it's bad design, which should have been fixed. A database product as
mature as SQL Server should support datetime precision in milliseconds
- at least! If it costs extra storage, who cares? GUIDs take lots of
storage, look how popular they've become. But give us the option.
The fact that SQL rounds to "one three-hundredth" of a second is
counter intuitive, and clearly shows that it is a design flaw, one that
has persisted to this day. What rational justification is there for
this behavior? Hundredths of a second, tenths, that I could see, but
every 3 milliseconds? Lame.
Maybe in the next version (SQL Server 2010) they can give us a new data
type called "bigdatetime", which allows greater precision. The old
datetime can be preserved for those purists who prefer working with
1/300 second intervals.|||Chris Durkin wrote:
> <snip>
>
> What datatype are we supposed to use - is there a new datetime type in
> 2005 with more precision?
You need to come up with your own scheme: perhaps use an int column to store
the number of milliseconds since midnight...something like that
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||>> A database product as mature as SQL Server should support datetime preci
sion in milliseconds - at least! <<
Actually, the FIPS-127 specs required at least 5 decimal places in a
TIMESTAMP; more would be better and it is getting a LOT easier to do.
I have a cheap alarm clock that sets itself to in milliseconds using
the NIST radio signal.
This is implementation is a "code museum" problem. Sybase started on
UNIX, which did a "clock tick" model of time. DB2 uses a "Cobol" model
of time which keeps separate fields for
"year-month-day-hour-minute-second-subseconds", etc.
But I agree that this is a serious flaw when you are trying to sell
your product for enterprise level and DW apps.

permissions gone missing in sysprotects

Hi there,
I have found the bugs when upgarded DB from sql 7.0 to sql 2000 and I
noticed that load of records have gone missing in sysprotects table (
something like from 5000 records down to 60 records only).
As a results, permission setting does not appear in objects permission
management in Enterprise Manager.
I know that the permission setting is still hold in syspermissions table,
but without the "green ticks" appears on the Enterprise manager interface, w
e
would not be able to know what permission we have assigned to each role or
user at all.
This problem only happen with the database that has been moved from 7.0 to
2000 though , if you move db from 2000 to 2000, it seems to be fine.
Does anyone else out there know how to fix this ?
cheers
JackI have never heard of this one.
One way I could see having problems is if you had modified
system tables. That's the only scenario I can think of but I
would have thought you'd get an error in the upgrade
process.
As already suggested in one of the other groups, you should
contact Microsoft Product Support if you feel you have a
bug.
-Sue
On Wed, 26 Jan 2005 20:45:02 -0800, "Jack Yao"
<JackYao@.discussions.microsoft.com> wrote:

>Hi there,
>I have found the bugs when upgarded DB from sql 7.0 to sql 2000 and I
>noticed that load of records have gone missing in sysprotects table (
>something like from 5000 records down to 60 records only).
>As a results, permission setting does not appear in objects permission
>management in Enterprise Manager.
>I know that the permission setting is still hold in syspermissions table,
>but without the "green ticks" appears on the Enterprise manager interface,
we
>would not be able to know what permission we have assigned to each role or
>user at all.
>This problem only happen with the database that has been moved from 7.0 to
>2000 though , if you move db from 2000 to 2000, it seems to be fine.
>Does anyone else out there know how to fix this ?
>cheers
>Jack|||Hi,
By any chance were these permissions on system objects in master.dbo?
AFAIK these were never preserved during upgrades.
Note - In Yukon, we now preserve permissions on system objects during the
upgrade process. So if you DENY EXECUTE TO PUBLIC on master.dbo.xp_cmdshell
,
we will remember that during the upgrade.
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
"Sue Hoegemeier" wrote:

> I have never heard of this one.
> One way I could see having problems is if you had modified
> system tables. That's the only scenario I can think of but I
> would have thought you'd get an error in the upgrade
> process.
> As already suggested in one of the other groups, you should
> contact Microsoft Product Support if you feel you have a
> bug.
> -Sue
> On Wed, 26 Jan 2005 20:45:02 -0800, "Jack Yao"
> <JackYao@.discussions.microsoft.com> wrote:
>
>|||Hi Clifford ,
the missing permissions are not in the sysprotects in Master db either. Bear
in mind that these missing permissions are set specificly to the user
assigned to my production database, and these users do not exist in Master
database anyway.
As I said before , I noticed that, everytime I set permission to the
production database, it only goes into syspermissions, so I wrote this scrip
t
to list out permission on my production database, and it works fine.
********************
select sysusers.name [USER_NAME], sysobjects.name [OBJECTS] ,
case actadd
when 1 then 'SELECT ONLY'
when 2 then 'UPDATE ONLY'
when 3 then 'SELECT + UPDATE'
when 4 then 'DRI'
when 5 then 'SELECT + DRI'
when 8 then 'INSERT ONLY'
when 9 then 'SELECT + INSERT'
when 27 then 'SEL+INST+UPDT+DEL'
when 31 then 'SEL+INST+UPDT+DEL+DRI'
when 32 then 'SP EXECUTED'
END [PERMISSION]
from sysobjects
inner join syspermissions
on sysobjects.id = syspermissions.id
inner join sysusers
on sysusers.uid = syspermissions.grantee
and sysusers.name = 'myusername'
order by objects
***********************
So even though I cannot see the green tick in EM interface, I can still see
the setting permissions from that scripts.
Still, there is no way to resolve this issue, as far as I know anyway .. :-(
Jack
"Clifford Dibble" wrote:
[vbcol=seagreen]
> Hi,
> By any chance were these permissions on system objects in master.dbo?
> AFAIK these were never preserved during upgrades.
> Note - In Yukon, we now preserve permissions on system objects during the
> upgrade process. So if you DENY EXECUTE TO PUBLIC on master.dbo.xp_cmdshe
ll,
> we will remember that during the upgrade.
> Regards,
> Clifford Dibble
> Program Manager
> SQL Server Engine
>
> "Sue Hoegemeier" wrote:
>|||HI Jack,
Can you run the following query and report the result?
select id, uid, type from sysobjects where name = 'sysprotects'
select * from syscolumns where id = object_id('dbo.sysprotects')
Thanks
Andrew
SQL Server Engine
"Jack Yao" wrote:
[vbcol=seagreen]
> Hi Clifford ,
> the missing permissions are not in the sysprotects in Master db either. Be
ar
> in mind that these missing permissions are set specificly to the user
> assigned to my production database, and these users do not exist in Master
> database anyway.
> As I said before , I noticed that, everytime I set permission to the
> production database, it only goes into syspermissions, so I wrote this scr
ipt
> to list out permission on my production database, and it works fine.
> ********************
> select sysusers.name [USER_NAME], sysobjects.name [OBJECTS] ,
> case actadd
> when 1 then 'SELECT ONLY'
> when 2 then 'UPDATE ONLY'
> when 3 then 'SELECT + UPDATE'
> when 4 then 'DRI'
> when 5 then 'SELECT + DRI'
> when 8 then 'INSERT ONLY'
> when 9 then 'SELECT + INSERT'
> when 27 then 'SEL+INST+UPDT+DEL'
> when 31 then 'SEL+INST+UPDT+DEL+DRI'
> when 32 then 'SP EXECUTED'
> END [PERMISSION]
> from sysobjects
> inner join syspermissions
> on sysobjects.id = syspermissions.id
> inner join sysusers
> on sysusers.uid = syspermissions.grantee
> and sysusers.name = 'myusername'
> order by objects
> ***********************
> So even though I cannot see the green tick in EM interface, I can still se
e
> the setting permissions from that scripts.
> Still, there is no way to resolve this issue, as far as I know anyway .. :
-(
> Jack
>
> "Clifford Dibble" wrote:
>|||Hi Andrew,
sorry for the mess, it is difficult to show you the results of second query
in proper format since it has too many columns to show in this tiny space
here, but here we go:
the query select id, uid, type from sysobjects where name = 'sysprotects'
return the following results :
id uid type
23 1 S
the query "select * from syscolumns where id = object_id('dbo.sysprotects')"
return:
id 23 56 1 56 4 10 0 1 4 0 0 0 0 0 0 1 N
ULL 2 NULL -1553186121 0 56 7 NULL 1
0 0 0 0 0 NULL NULL
uid 23 52 1 52 2 5 0 2 8 0 0 0 0 0 0 2 N
ULL 6 NULL -1553186121 0 52 6 NULL 5
0 0 0 0 NULL NULL
action 23 48 1 48 1 3 0 3 10 0 0 0 0 0 0
3 NULL 8 NULL -1553186121 0 48 5 NU
LL 3 0 0 0 0 NULL NULL
protecttype 23 48 1 48 1 3 0 4 11 0 0 0
0 0 0 4 NULL 9 NULL -1553186121 0 48
5 NULL 3 0 0 0 0 NULL NULL
columns 23 165 2 165 4000 0 0 5 -1 0 0 0 0 0 0 5 NULL -1 NULL -1553186121 24
37 4 NULL 4000 NULL 0 0 1 NULL NULL
grantor 23 52 1 52 2 5 0 6 12 0 0 0 0 0
0 6 NULL 10 NULL -1553186121 0 52 6
NULL 5 0 0 0 0 NULL NULL
Please let me know how you go with it
Jack
"Andrew Zhu" wrote:
[vbcol=seagreen]
> HI Jack,
> Can you run the following query and report the result?
> select id, uid, type from sysobjects where name = 'sysprotects'
> select * from syscolumns where id = object_id('dbo.sysprotects')
> Thanks
> Andrew
> SQL Server Engine
> "Jack Yao" wrote:
>|||Hi Jack, I have experienced the same issue. On my site it was caused by
rights being granted to any object that has a type other than table,
view or proc. Use the following script to detect.
select b.name, b.type, a.* from syspermissions a, sysobjects b where
a.id = b.id
and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr')
order by b.type
To resolve, you must remove the offending syspermissions entries before
upgrading. As soon as SQL2000 the upgrade hits one of the problem
entries it stops populating sysprotects. I cant see any way to resolve
the issue after the 2000 upgrade.
Use the following script to delete the problem entries.
sp_configure 'allow updates', 1
go
reconfigure with override
go
delete from syspermissions where id in (select b.id from syspermissions
a, sysobjects b where a.id = b.id
and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr'))
go
sp_configure 'allow updates', 0
go
reconfigure with override
go
Hope this helps.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Derek ,
That is exactly what I was talking about , the reason why sysprotects stop
populating the records.
Sadly , my situation is in the limbo since I have already gone through the
upgrade and the records in sysprotects are long gone (so re-upgrade ain't
gonna do any good).
Nevertheless , for the love of SQL :-) , do you habppen to know why SQL
stop populating sysprotects as soon as it hits those offending permissions ?
I mean, what make granting permission to those objects offend SQL at the
beginning with ?
ta
Jack
"Derek" wrote:

> Hi Jack, I have experienced the same issue. On my site it was caused by
> rights being granted to any object that has a type other than table,
> view or proc. Use the following script to detect.
> select b.name, b.type, a.* from syspermissions a, sysobjects b where
> a.id = b.id
> and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr')
> order by b.type
> To resolve, you must remove the offending syspermissions entries before
> upgrading. As soon as SQL2000 the upgrade hits one of the problem
> entries it stops populating sysprotects. I cant see any way to resolve
> the issue after the 2000 upgrade.
> Use the following script to delete the problem entries.
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> delete from syspermissions where id in (select b.id from syspermissions
> a, sysobjects b where a.id = b.id
> and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr'))
> go
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> Hope this helps.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Hi Jack, my guess is that it stops populating sysprotects because SQL
server isn't designed to allow the setting of permissions on objects
like triggers, constraints, primary keys etc. You can't do it through
Enterprise Manager on 7.0 or 2000.
I have traced the problem on our system to a bad script that allocated
all permissions to all objects. During the upgrade, SQL 2000 probably
doesn't know what to do with the offending permission records and just
stops the load of sysprotects, which in turn means no further records
will go in when new permissions are assigned after the upgrade.
If you have already upgraded to 2000, I guess you could fix it by
creating a new database and copying over the objects and data, and then
setting the permissions. That way you have a new sysprotects table.
Cheers
Derek
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Derek ,
not sure what you mean by "create new database and copy over the objects and
data".
Did you mean copy it with the copy database wizard or use the old
fashion "backup/restore" manually ?
Jack
"Derek" wrote:

> Hi Jack, my guess is that it stops populating sysprotects because SQL
> server isn't designed to allow the setting of permissions on objects
> like triggers, constraints, primary keys etc. You can't do it through
> Enterprise Manager on 7.0 or 2000.
> I have traced the problem on our system to a bad script that allocated
> all permissions to all objects. During the upgrade, SQL 2000 probably
> doesn't know what to do with the offending permission records and just
> stops the load of sysprotects, which in turn means no further records
> will go in when new permissions are assigned after the upgrade.
> If you have already upgraded to 2000, I guess you could fix it by
> creating a new database and copying over the objects and data, and then
> setting the permissions. That way you have a new sysprotects table.
> Cheers
> Derek
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>