Wednesday, July 15, 2009

Fairy Repellent

One of my big peeves is devs writing code to protect themselves against things that never happen.

You really see it all the time don’t you?  And it always sounds so reasonable at the time, but it really isn’t if you put any thought into it at all.  Here are a couple examples.

We had a dev a while back to introduced a redesign for a job where he put everything pretty much into the same step.  He had a long SP written that combined xp_cmdshell steps and reindexing steps, etc.  It was all just one long strip of code.  And of course his logic sounded logical on the surface, but not once I started asking the right kinds of questions.  See, his reasoning was that since there’s no way in SQL to run a job step by itself and not the steps that come after it, he needed the ability to be able to run any section of that SP he needed.  Ok, that sounds alright because he’s right.  While you can start a job at any step, you can’t tell it to only run a single step unless you play with the workflow in the steps. 

But one simple question killed the whole thing… when’s the last time you needed to run just a single step in this job?  I assume that the steps that follow are there for a reason, right?  So give me a scenario that this would cover.  And of course it’s always the ethereal… I can’t think of anything right now, but I’m trying to cover all the contingencies.  I get that dude, but you’re coding for issues you can’t even state. 

What about when the job fails?  How often does the job fail? 

He said, oh it probably fails a couple times a week for one reason or another. 

Ok, now how easy is it going to be to troubleshoot the failure if everything’s in just in one huge pile?  Shouldn’t you be coding for the case that happens the most?  And of those times it fails say at step 2, do you ever need to run step 2 and nothing after it? 

He said, No. 

, then why are you coding for it? 

He says, well there might be a time when I need to run a single piece of the code for some reason say redoing something in the middle of the day or something.  It hasn’t happened yet, but it could. 

Yeah sure, that could happen.  And if it ever does what’s stopping you from copying the code from the job step you need to run and just running it manually in SSMS? 

To which he replied… Ummmm….

So ok, this is getting long so I’ll leave this at one example, but you get the point.  It’s a lot like buying fairy repellent for your house, and setting fairy traps everywhere.  When’s the last time you had a fairy problem?

Watch my free SQL Server Tutorials at:

Read my book reviews at:

Blog Author of:
Database Underground –

Follow my Twitter:


Technorati Tags: ,


About Me

My Photo
Sean McCown
I am a Contributing Editor for InfoWorld Magazine, and a frequent contributor to as well as I live with my wife and 3 kids, and have practiced and taught Kenpo for 22yrs now.
View my complete profile


Blogumulus by Roy Tanck and Amanda Fazani

Page Views