June 27, 2005 at 6:41 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/anotherdbawhoops.asp
June 27, 2005 at 8:35 pm
This could be the 2nd of a very long series of articles (doesn't have to be all of your little mishaps). Maybe we should start another discussion forum for those incidents. It's fun to see that some little changes can have drastic repercusions and gives us a chance to gain more experience..
July 5, 2005 at 8:28 pm
Unfortunately, you are correct. I have more to tell .
Feel free to submit your own. Or am I the only one that's made this many mistakes?
July 5, 2005 at 8:31 pm
Mine aren't as funny .
July 5, 2005 at 9:52 pm
I find that dates are the biggest problem with SQL Server.
On one of our server, an application was written with the US date format in mind ie MM-DD-YYYY
Yet, we're in Australia and we've since put other databases onto this server which work with DD-MM-YYYY
The server is constantly in a thro changing from one format to another.
Every query needs the SET DATEFORMAT command prepended to the date specific queries.
If only MS had done the right thing and used a date format like YYYY-MM-DD ... like MySQL does, which works perfectly.
July 6, 2005 at 12:48 am
Hi Steve
Hope I got this right. So you basically use a statement like this:
select top 5
o.orderid
, o.customerid
, CONVERT(char(10), o.requireddate, 101) as converteddate
from orders o
order by requireddate desc
Which will result in the correct sort order because you are sorting the original column.
Regards
hot2use
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
July 6, 2005 at 12:56 am
This is the classic mm/dd/yy versus the yy/mm/dd format.
As steve jones said when you do a convert(variable,101) it will cast to a string and hence it will bring 12/31 which is greater than 06/07.
The trick is use the following query to get you the same set of records everytime
select top 5
o.orderid , o.customerid
, CONVERT(char(10), o.requireddate, 120) as requireddate
from orders o
order by requireddate desc
***-- note the value of 120
July 6, 2005 at 2:34 am
We originally had the same problem as the guy in Australia with dates in different country formats. The SET DATEFORMAT method is one solution but a better solution is the International format if you are entering any dates convert them to format 112, so that 13th January 2005 becomes '20050113'.
July 6, 2005 at 3:15 am
These offer another solution that lets you keep the same column name :
select top 5
o.orderid
, o.customerid
, CONVERT(char(10), o.requireddate, 101) as requireddate
from orders o
order by convert(char(10), o.requireddate, 120) desc
or :
select top 5
o.orderid
, o.customerid
, CONVERT(char(10), o.requireddate, 101) as requireddate
from orders o
order by cast(o.requireddate as datetime) desc
July 6, 2005 at 6:13 am
To help out Steve, my worst moment had nothing to do with Sqlserver or DBA duties, but it was a communication issue. We had just spent 23 hours straight rebuilding a failed server from the server right next to it. At 4:00 am my partner said, "we' re done, pull the power cord on the right" and I did. Unfortunately, we were looking at each other across a shelf. I got the "honor" of explaining to the VP what happened.
July 6, 2005 at 6:25 am
I'm assuming that the new rebuilt server was shut down here... did you have to rebuild it again ??
July 6, 2005 at 6:33 am
My worst DBA Whoops was when I had copied a productive database to the training environment and somehow forgoten to check the documents share directory in the settings table of the database.
A few hundred documents were rewritten on the document share because the training environment was using the productive environment's share. Had to restore and merge a few hundred documents.
Another not so bad Whoops was restoring a database from one server to another and forgetting to change the filenames And gone was the productive database.
My motto now is check and check again. And if you aren't sure then cancel what you were doing and start over. Never had to explain myself since then.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
July 6, 2005 at 6:36 am
I was thinking that if your order numbers are sequential, which ours are, you could sort by order number and avoide the problem of sorting on the converted data column. That way you would have the returned data sorted by most recent. This would only work, however, if the order numbers are sequential.
July 6, 2005 at 6:38 am
Yep, the rebuilt server was shutdown. This incident was a few years ago when servers were the size of small refrigerators. My partner and I were looking at each other over a shelf that had been built to hold the servers. My right was his left. After another 23 hours the server was rebuilt and I had to explain the almost 3 day outage of a critical application. The reason for my post was to illustrate Steve's point that we all make some pretty stupid mistakes, but not all "Technology " mistakes are technical, a 20 second pause on my part at the right time to confirm and I am a Hero, instead it was another sleepless night and a
July 6, 2005 at 6:41 am
Would've love to see the face of your partner when you pulled the wrong cord .
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply