Doug Lane

SQL Server Entertainer

Knowing your data is better than believing it

by

“You’re sure, or you know?”
-Stanley Tucci in the BBC movie “Conspiracy“

As developers, DBA’s, and analysts, we’ve all been asked this question in some form at one time or another.  We know the feeling of queasy uncertainty that comes with responding, “I’m sure.”  We know the sturdy confidence that comes with responding, “I know.”  So why would we ever opt to answer anything but, “I know”?

There are advantages to being sure about our work:

  • We get more work done in the same amount of time.
  • We deliver work with low risk of error.
  • We develop a reputation for delivering work faster or ahead of schedule.

But consider the consequences:

  • Our work is more likely to need revision, so the perceived time savings is an illusion.
  • Business costs increase because of decisions made on our errant data.

And most importantly…

  • We develop a reputation as producing unreliable output.
Be careful, stick figure!
Don’t say we didn’t warn you

In a mission-critial environment, this last bullet point is really all that matters.  As soon as your company can count on not counting on you, you’re doomed.  Your career path there consists of two feet of sidewalk and a large brick wall guarded by a mean dog.

Fortunately, if you haven’t done incalculable damage to your reputation or your company, here are three things you can start doing immediately to remedy the situation.

1. Ask questions until you know the question being asked

There’s a process many of us have been through that goes something like this:

(client sketches dataset -> you query -> you report -> client asks why it doesn’t look right -> missing parameter discovered)

The answers are right but the question is wrong.  We can spend several hours looping through the diagram above, or loop through step 5 (missing parameter discovered) and arrive at the coda in several minutes.  Which would you rather do? (Don’t answer that.)

Once you’ve arrived at all the parameters, show your client the list of parameters and the expected look of the output.  If they agree it looks right, congratulations.  You’ve saved everyone involved a lot of time by understanding the question up front.

2. Check your work

No, I mean really check it.  Yes, it will take more time.  Tell your boss you’re going to need a little more time to verify or test your work.  In a mission-critical environment, no rational manager will trade consistently that little extra time in return for increased skepticism of your work, your reputation and theirs.

3. Admit your errors quickly to all concerned

Everyone screws up once in a while.  As important as it is to get it right the first time, it’s equally important to react with urgency when you don’t get it right.  I once sent out bad data to twenty-two department administrators.  As soon as I realized the mistake, I sent e-mail to each of them explaining the mistake, apologizing, and telling them when to expect corrected data.  Then I went down the phone list and called each one to make sure they’d seen my e-mail.  While I lost some credibility for providing the bad data in the first place, many of the admins expressed appreciation for my efforts to inform them of the error.  As a result, those admins know that when my data doesn’t have integrity, at least I will.

While we can’t promise perfection to the people who depend on our data, we can at least give them the trust that comes with saying, “I know.”

How to tell if a report is empty without opening it

by

Report Half Full
I prefer to think of the report as half full.

Once in a while, I’ll run a scheduled report and for whatever reason, the report won’t return any data.  It ran just fine — no errors, no failures — but there’s just no data in the body.  The reports are saved into various folders on the network.  In thumbing through these folders, I can spot the reports that ran with no data quickly.  It’s the ones that have a certain file size.

In my case, the report in question renders at 67 KB if it’s empty.  Dashboards with graphs, sparklines, and other fixed-size objects will show a little variance in size if a section is empty.  Table rows, on the other hand, will have a much greater impact on the file size when they are populated.  The exaggerated file size difference then makes spotting the blank ones much easier.

So, next time you open a report and you find the content to be empty, take note of the file size.  That number will raise a red flag for you whenever you see it and hopefully help you to catch (and correct) it before your users do.

How to keep Report Server awake

by

Server Rack
It's not asleep, it's pining for the fjords.
There are few things more frustrating than running a demo of a new report for your users, telling them how fast it is, then having Report Server deliver your three-second report in thirty-three seconds because the server had to wake up first.  Until Microsoft makes the sleep setting for RS configurable to something other than twenty minutes, the best solution I have heard comes from Steve Wake of the Denver SQL Users Group.

Steve said his company runs a report every fifteen minutes to keep the server at attention.  I didn’t ask what was in the report, but I’m assuming it’s one of two things:

  • The report is blank, so from a code standpoint it can’t fail, and it won’t consume any resources beyond the bare minimum.
  • The report is for monitoring or auditing, and needs to be run every fifteen minutes anyway.

In either case, it’s a simple and practical solution and I couldn’t help but grin when I set it up on my server.

E-mail address strings in SSIS

by

I came across an important distinction in address format while working on a script task.  The script task sends an e-mail using the System.Mail methods and I had been using the same address string as I had used in a Send Mail task upstream.  Turns out, for whatever reason, addresses in a Send Mail task are separated by commas, whereas addresses in a MailMessage.To need to be separated by semi-colon.