Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

Dear DBA ….

Dear DBA –

Thanks so much for helping us developers out with the latest changes you've made to the stored procedures in our system.  While it may have been nice if we got together first to discuss these changes, I do appreciate that you worked very hard to make things much easier for us.

Despite the great changes you've implemented to assist us, I am afraid I must ask for further help in a few matters.  I appreciate your patience.

1.    Rewriting all procs to format date values for us automatically as "mm/dd/yyyy" has really been wonderful.  However, for some reason, the data returned by those procs is no longer sorted correctly!  For example, January 2005 is being returned before February 2004!  Not sure why this is happening.  Probably an error on my end, I am having the other developers look into it.  We tried to do the sorting in our apps, but that doesn't work either .. for some reason, the DataTable that caches our data simply won't sort the dates properly.  Weird!  We opened a support ticket with Microsoft on this one, we'll keep you posted.

2.    Sometimes, we need some dates formatted as "mm-dd-yyyy", or even "Monday, May 13, 2005."  Can we write more versions of the existing stored procs to return dates in those formats as well?  Maybe come up with a code name for each, and append it to the end of each stored proc?  i.e.,  "ReturnCustomerInfoDateFormatB" or something.  That would be great!

3.    I am also having trouble getting some of my numeric formulas to work.  Since you decided it would be easier to format our money values for us by having the stored procs return VARCHAR's formatted like "$1,348.56"  (commas and everything! thank you!!!) the silly report designer I use and also my .NET apps are having trouble sorting and adding up these values.  I figure it is something I am doing wrong or a bug in the software we have written; we are working on it.  Also, the right-aligned values definitely look great, but we need to increase the column width on certain reports, if you don't mind.  I'll give you the full specs for the different reports and web pages in a separate email later today.

4.    Another small favor: sometimes we need to round things to the nearest dollar.  Perhaps we can try the same thing with our "date formats" and create a code system for money formats as well?  i.e., "ReturnCustomerInfoDateFormatBNoDecimalPlaces" and so on.  This will really help us out, right now we need to parse the formatted numeric values to remove the $ and commas and find the decimal place and remove all the characters after it to do this, but that is really pretty confusing and inefficient, so your help would be greatly appreciated … Thanks in advance! I don't know what we'd do without you!

5.    Returning useful text instead of those pesky bit values is great!  However, sometimes we forget if a particular stored procedure is returning "Yes" or "True" or "true" or "Active" or whatever else for TRUE values, so sometimes our code doesn't work right when we do comparisons, and if it does work right, we need to change our code everywhere if we decide to output "Y" instead of "Yes" ….  As a solution, we changed all of our code to do things like

if (val.ToUpper()=="TRUE" || val.ToUpper()=="YES" || val.ToUpper()="ACTIVE" || val.ToUpper() = .. )

everywhere, but that has been difficult to maintain ….. we'll have to make a standard library function to handle this. Or, maybe we can add a notation to indicate the boolean text formats returned to the various stored procs names as well?  Or should we have a standard convention – always return "Yes" for true, and then in our apps we can simply check for the string value "Yes" and replace it with whatever text we need, and likewise for false.   Whatever you think is best would be great!  Either way, not having to deal with tricky booleans has been wonderful and we thank you.

6.    I tell you, being able to remove all group headers and footer from our reports has been a GODSEND !! Thank you so much for figuring out how to do this in T-SQL.  We must have screwed something up when we rewrote our reports, however, since now things run very slowly.  I will look into this, course.  By the way, we have a list of 10-15 minor grouping changes to make on some reports, should I send them to you via email?  Tony in accounting has some, too.  Just minor tweaks like indenting and all that, I'm sure it will be easy for you to maintain.  Also, I think we need a newer version of Reporting Services or a bug fix or something, because for some reason the aggregate functions and sorting features suddenly stopped working or the report acts weird when we try to use those.  Maybe we can just send you all the different possible sorts and aggregate calculations we need and you can incorporate them into a set of stored procs for us? That would be great. Let me know the best way to proceed on all this!

7.    Oh yeah, that reminds me – some of our customers using our windows apps in the UK are experiencing some weird issues with dates for some reason.  They complained that even though their PC's had specific dd/mm/yyyy date settings, the app no longer followed them.  I can't figure this one out – maybe we need more stored procs for them, too?  how about putting a "@InUK bit" parameter in all of them or something, which we can set when we want to use UK date formatting?

Anyway, thanks again for all of your help, you have really made the lives of us developers much easier by not asking us to worry all these very complicated front-end tasks!

- Your developers

see also:

Legacy Comments

re: Dear DBA ....
If I was your DBA Jeff that would all be done already but not sure it would have been a issue in the first place :)

re: Dear DBA ....
This is just awesome. Thanks for making my day a little better :-)

Paul Vallee
re: Dear DBA ....
Dude, I'm sorry for your suffering. Posted to my blog about this entry, see url above.

Good luck and best wishes,

Paul Vallee, Pythian.

re: Dear DBA ....
meh, shitty RDBMS, shitty "DBA", shitty "developers"

re: Dear DBA ....
Nicely done.

re: Dear DBA ....
Dear developers,

Thank you very much for your words of praise and aknowlegment of my efforts to make your job easier. I appriciate your comments and if I had time to write all those procedures, I assure you, I would do that the same moment. I would even give you access to previous versions of stored procedures but unfortunatly I forgot to make a backup before doing changes.

Now, if you will excuse me, i must attend to some more important stuff. I guess you dont think that I only sleep and chat at work!

Sincerely yours,

Bob mycroft
re: Dear DBA ....
To be honest, the developer deserves everything he gets if he lets DBAs edit their application. Thats what *developers* are for. thats what code reviews and change control are for.

Most DBAs cant code as they were never developers in the first place. Im a DBA and an ex-developer with about 12 years experience and *I* dont edit application code, although I will on occasion take festering brown pieces of SQL to the developers and say "change like *this* and knock off 25% of the load on the system IF YOU DONT MIND".

Personally i would take your DBA out and beat him with a heavy stick for bringing the rest of us into disrepute.... Im an Oracle DBA by the way ;-)

re: Dear DBA ....
Surely you had some agreed technical specifications of the procedures that you wrote your apps against? If not, it was just as much YOUR fault, for not seeing this coming. It's like: "I climbed up the ladder, but it slipped and I fell! [but I didn't bother to secure it properly] ...

re: Dear DBA ....
Good joke.
Shame too many posters with no sense of humour

re: Dear DBA ....
Conversation with my DBA:

DBA: Your database is huge, it has grown to 15 gig.
ME: Don't you have a maintenance plan running on it with shrink? It really should only be less than 1/2 gig.
DBA: Yes. I will check on it.

2 days later...

DBA: The job runs succesfully every night.
ME: Well, we decided to do some archiving of data and cleaned out 2 years of data for you.
ME: Yesterday it was 15 gig. After the maintenance plan/shrink last night, it is still 15 gig.
DBA: I'll check into it.

2 days later...
ME: It is still 15 gig.
DBA: I'll checl into it.

1 day later
ME: Wow, you must have done somthing, now the db is only 255 mb. response


Someone who dislikes DBAs
re: Dear DBA ....
DBA's are completely useless, and make every developers life hell.

Someone who dislikes DBAs
re: Dear DBA ....
D - Don't
B - Bother
A - Asking

re: Dear "database" developers ....
"Oh, right. That makes perfect sense. Turn right to go left. Yes, thank you! Or should I say, no thank you. Because in Opposite World, maybe that really means thank you." (Lightning McQueen, Cars)

If I could add "8. Thanks for using cursors everythere, it really makes sense.", it would be a typical letter from a DBA to "database" developers (despite the fact that some of them are really good developers without "database" prefix)
Besides, it would follow pattern: