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 for developers not working after 2005 upgrade

Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.

After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?

Can you please elaborate on the access difficulty? Are you encountering errors and, if yes, what are those errors? It would help if you could give us an example of some action that used to work and now doesn't, and of what is the system response in this case.

Thanks
Laurentiu

|||

They are making a connection through Visual Studio using the MS OLE DB Provider for SQL Server with their domain account and receiving the following error...

SELECT permission denied on object 'Contact_Info', database 'GetLean', schema 'dbo'.

|||

Could you check what is the current execution context at the time when this error is obtained? You can use Profiler to figure out what is the current execution context.

There are two possibilities:

(1) current execution context is not a member of db_datareader, so you do not have SELECT permission.

(2) current execution context is explicitly denied SELECT permission on the Contact_Info table.

If you determine the current execution context, then you can check whether it's a member of db_datareader by looking at the sys.database_role_members catalog. You can check for the SELECT permission being denied by looking at the sys.database_permissions catalog.

Thanks
Laurentiu

|||

They are a member of the db_datareader, db_datawriter, db_ddladmin, and db_securityadmin roles. There are no permissions explicitly denied in this database.

Even though they have db_datareader, db_datawriter, db_ddladmin, and db_securityadmin rights....must I still assign them to the dbo schema?

|||

db_datareader grants select on the entire database, hence on the dbo schema as well (http://msdn2.microsoft.com/en-us/library/ms189612.aspx). You don't need to do a special permission grant for the dbo schema.

Can you try a little experiment? Create a test table in the dbo schema and then verify if those members of db_datareader can access it. Also, create a separate schema and a table in it and see if there is the same behavior for it as for the dbo schema.

Also, does this happen for all those developers or only for some? If some of them were added to db_denydatareader role, then that would prevent them from selecting from anything. Are they members of other roles than those four that you mentioned?

Thanks
Laurentiu

|||

It turned out not to be a DBA problem. Sorry, should have posted the resolution earlier.

It turned out to be a developer issue...a combination of failing to practice current standards and inexperience with Visual Studio. Aargh!

Permission to execute Stored Procedure

Which fixed database role allows a user to execute a user defined stored procedure while minimizing the amount of permissions given. I think db_Datareader will do the trick.Sorry, but db_datareader doesn't have permission to execute ANY stored procedures, although they can read any table or view (more accurately, db_datareaders has SELECT permissions on every object in the database).

The only group that has execute permissions on all of the objects in a database is db_owner. That's WAY too big a hammer to swing without considerable forethought, since it gives the user the "keys to the kingdom" as far as that database is concerned. They can do anything they please with any object in the database, which is a lot more than what you probably intended.

One crude way to work around this might be to create a db_runner group within your database, and schedule a job to grant permission to that group to execute every procedure and function in your database. You could run the job every few minutes, and get 99% of what you are looking for, and you could also exclude objects within your GRANT script if you see fit.

-PatP