First time here? You are looking at the most recent posts. You may also want to check out older archives or the tag cloud. Please leave a comment, ask a question and consider subscribing to the latest posts via RSS. Thank you for visiting! (hide this)

Almost 2 months ago Steve Harman, showed a ugly character map used in a database field to store the week's working days.

Today I came across something similar, or maybe even worse, since the super-smelly database field is used in some system SPs in SQL Server 2000.

I found a few examples of inconsistencies and "strange" implementations but let's focus on just one system stored proc: sp_help_job.

This SP is used to retrieve a list of jobs or, if called specifying a GUID, it returns 3 result sets with the steps and schedules of a single job.

Let's see some examples:

enabled, which can be only 0 or 1 in the list of jobs is returned as tinyint, while in the list of steps and schedules is returned as int.

notify_level_****, which accepts values from 0 to 3, is an int in the result set with the detail of a job, but a similar field in the result set with the list of steps, named on_success_action, is a nvarchar(4000). Why a string 4000 chars long for a value that can range from 1 to 4? Because the actual value returned by the SP is the value followed by the meaning: "1 (quit with success)". This is cool if the SP is run by a DBA that read the screen, but not if the results are read by a software. And also, why 4000 chars? And there are other fields using this mixed value + description approach.

And let's see one last example: if you want to return the start date of a scheduled task what datatype will you return? A datetime as you use when you pass the start date into the sp_add_job? No... that would be too easy. A string representation of the date, like 20070131, so a nvarchar(8)? Here you are getting closer to the result. The SP returns a value that is written 20070131, but it's read 20 millions, 70 thousands and one hundred 31, so it's an int. And the same applies to the start time and end date/time, but not with the creation date, which is a real datetime.

Once you know and once you find out all the details it's not complicate to deal with all that strangeness, but these are the weirdest approaches I've seen in life.

Here is an online version of the SQL Book with the description of the sp_help_job stored procedure. It's for SQL Server 7, but the sp is the same in SQL Server 2000 (and hopefully 2005).

posted on Thursday, January 31, 2008 1:30 AM
Comments have been closed on this topic.