Another DBA Whoops
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/anotherdbawhoops.asp
-
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..
-
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?
-
Mine aren't as funny
. -
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.
-
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. -
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
-
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'.
-
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
-
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.
-
I'm assuming that the new rebuilt server was shut down here... did you have to rebuild it again
?? -
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. -
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.
-
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 -
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