Another DBA Whoops
-
Access is not so bad. Managers + Access, now that's dangerous.
-
Would now be a good time to bring up the "SQL-Server-2000-only-recognizes-every-3rd-millisecond" limitation? That one used to bite me in the butt. BOL covers this topic for those who are interested (search "datetime three-hundredth" and click the "datetime and smalldatetime" topic in the search results). As an aside, check out this article at SQL Server Performance for other revelations into the datetime data type: http://www.sql-server-performance.com/fk_datetime.asp
-
This Just won't work.
order by o.requireddate desc
won't work.
-
Hi Steve,
Hope I got this right. So we can use a statement like this:
select top 5
o.orderid
, o.customerid,requireddate
, CONVERT(char(10), o.requireddate, 103) as requireddate
from orders o
order by CONVERT(DateTime, o.requireddate, 103) Desc
I tried it and its working fine.
Regards,
Kiran Patil
-
Hey Steve, I think you should have posted the solution in the article... might have saved 35 messages on this thread
. -
That's less fun. Besides, I think I learned a few things by reading all the posts
I ended up changing the alias name so that I could order correctly by the column. Since this particular piece of code is only referenced in a couple places, it was easy for my to alter the code to recognize the new name.
-
Would there be something wrong with just using three columns? year / month / date?
and forgetting the date type altogether?
********************************
As for whoops...
I would put forward everything I've EVER done is an eventual whoops....
as nothing escapes a reboot...
hence the nickname ... gpf.....
mind you there were many more of them of a more immediate effect and I expect plenty of them to come ... generally nothing fatal... If not my posts will be less frequent, and it will learn me pretty good for using t-sql esp to control my tiburon ....
I have learnt more from failure then I have ever learnt from success....
*************************************
-
What does "gpf" means?
-
general protection fault... or page fault....
-
We had a developer that did not like NULLs (I don't either) and wanted the ability to leave dates blank. He kept defining date fields as varchar(10). This also avoided the problem of having to deal with times. It just made sorting by date a real B%^#.
We got tired of having to write convert statements in every order by clause and fixed all the data types. We now have a coding standard that says that fields will be defined using appropriate data types.
I still have to support legacy systems that have no date or datetime fields at all. They store dates as int in the form YYYMMDD. Those are sortable, at least. But then days between dates are .... And on it goes.
Our staff gets so tired of me harping about good, solid, database design. Its even worse when the guidelines are ignored and the project goes sour in the field. During the Post Mortems they keep expecting me to say "I told you so." I don't. I give them one of these
and go on.ATBCharles Kincaid
-
Yes there would. This also makes days between dates calculations a real pain.
I have learned to use SQL SERVER for the things that it is very good at doing. That is saving a retriveing my data. Sticking with the native types and using them as designed helps SQL SERVER work better. That means faster too.
Move all the formating stuff to the presentation layer. Crystal Reports or a good grid control (I love the Component One stuff) will save your sanity.
ATBCharles Kincaid
-
With 65 replies so far I have not been able to read them all (nor do I plan to) so I hope what I say isn't a repeat.
In my mind an easy to avoid these types of mistakes is to always view your query like this:
SELECT = A list of what data I want returned in what way/format
FROM = WHERE the data in the 'SELECT' clause comes from and how that data from different objects are related
WHERE = WHat are rhe conditions I place on the data returned
ORDER BY = What data from the objects in the FROM clause do I use to control the ordering of the items returned in the SELECT clause.
Always look at each section like that and make no assumptions about one based on something done in another and you'll avoid things like the ASCII Sort which is why the dates did not sort as expected but did sort as requested.
Ed
Kindest Regards,
Just say No to Facebook! -
Can some one please tell me why would any one put presentation login into SQL....
it's big no no in my book...
Borik
-
What do you mean "presentation login"?
If you mean to login to the database there may be a couple reasons...
The way I've coded my DBs with MS-SQL2K was have two database users. One being the DBO the other just a made up user name (I think WebUser was it). When someone logs into the web interface, they don't log in with WebUser, but they log in with a user name thats in a Users table that contains the individuals credentials. From there, I have the login processing page pull the credentials that the user is allowed to have (View customer contact info, permissions to write to notes, delete notes, adjust job information, etc).
The reason I do that is that even though SQL will verify user credentials, the user at the web end can't get access to the database directly, they can't talk to the master database directly, they can't use the SA user account because ASP code isn't checking for that login, so on and so on.
The other way some DBAs do it is that the credentials are checked at the database as far as having access to the individual tables. Gaining access to the DB in that regard I see as being very dangerous as now someone can attempt to guess what the SA password is. In a 3-teir application (Database, IIS, and Web) that I've written, I'll always leave it up to IIS to use limited accounts in the database.
To answer your question, its all going to come down to what the UI login screen is going to do when you hit SUBMIT or LOGON. If its going to create a connection object to the database using a database level user name, yeah, thats dangerous. If, however, its going to use a single global connection object using one limited database level user name thats limited in how it behaves with other databases, tables, and other elements to a database, then why wouldn't you have a login screen?
[EDIT]
Oh, as for my big whoops... I've got a development machine as well as a production machine. I always do a backup from the production to the development box, and make sure that I have functioning real life raw data required to do what I need to do. Part of the backup is to also copy not only the actual database, but the ASP code as well.
So I go in and restore the database to the development machine, copy the ASP code from the production machine to the development machine, and do my work. Well, I messed up royally by using a DELETE without a WHERE clause. Dumped the entire client table. So, I go and do the database restore again to the development box. I reloaded the web page to see if my screw up fixed itself, and it didn't. I open up the table and sure enough everything is there as it should be. So I'm very VERY puzzled as to what was going on. Then it dawned on me... I didn't change the global connection object on my development machine to look at the development machine database. Instead it was connecting to the production machine database. So I dumped the client list on the production machine, NOT the development machine. How fortunate I made a backup.
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply