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: