Tim Mitchell

Conditional Default Values in SSRS

Tim Mitchell — Tue, 07 Feb 2012 07:15:00 -0600

I’d like to share with you a ~~laziness~~ efficiency method I use when developing reports in SQL Server Reporting Services. I’ve found that it’s quite common to work on reports wherein the requirements dictate the following:

There are numerous parameters, and
The parameters will not use default values

Both are valid business requirements, to be sure. However, from the report developer’s perspective, the above requirements can translate into a time sink. When developing a report, a common method is to test the report very frequently as changes are made, which could eventually add up to hundreds or perhaps thousands of clicks just to set the default parameters during the various testing steps. Over the development life cycle of a single report, all that clicking around could literally add hours to the project!

Ideally, it’s best to eliminate this redundant selection of parameters for each test iteration. Certainly one could specify default parameters during the development cycle and simply remove the defaults, but this presents the issue of a) remembering to remove the default values from the report before deployment, and b) adding the defaults back to the parameters, should the report require additional work after deployment.

However, I like to use a method for conditionally setting default parameters that does not require predeployment changes. By leveraging a little VBScript in your parameter defaults, you can easily eliminate the need to manually set parameter values on each test execution of the report.

The method I’ll describe uses the following methodology:

Set up a Boolean control variable defining whether default values should be used
Set up default values for each parameter, and make the use of those values dependent on the control variable

The control variable essentially gives permission to each of the rest of the parameters to use the specified default values. If the control variable is not set to True, then no defaults will be used.

Demonstration

To illustrate this method, I’m going to create a report using a modified version of the AdventureWorks database. My test report is configured with 7 different parameters, all but one of which is required to have a value selected:

If I click really quickly (and the server is responsive), I can set all of the parameters to the values I want to use for testing in about 25 seconds. It doesn’t sound like much until you consider that, if the report logic or layout is complex, I might test the report in the SSRS preview window 50 times or more in a day as I make incremental changes. That’s about 20 minutes worth of clicking in a single day. Personally, I’d rather use that 20 minutes for something more productive!

Configure the Control Parameter

To save time (and potentially a carpal tunnel procedure), we’re going to add an additional parameter to the report. I’m going to call this parameter pUseDefaultValues, and configure it as a Boolean value:

You’ll notice that I’ve set the visibility to Internal, meaning that this new variable cannot be set externally. You might also consider setting this to Hidden if you choose to pass the value of the control parameter to the report (such as through a URL parameter), but for demonstration purposes we’re going to leave it as Internal and configure the logic to set the value at runtime.

To set the value of this variable, I’m going to use its Default Values tab. I’ll create a new default value for this control variable, in which I will set the value to True if a certain control condition is met. In this case, the most logical condition is to test the user ID of the account executing the report, which I can do by interrogating the SSRS built-in field value User!UserID. For demonstration purposes, I’ll keep it simple and just check this value to see if my own account is the one executing the report. If so, the variable pUseDefaultValues will be set to True.

As shown, you can use the value expression to test whether the user ID executing the report matches a specific string. The resulting True or False value will subsequently be consumed by downstream parameters as a flag to either use or discard the default values we’ll provide in the next step.

Remember that you can use other criteria for setting the control parameter value – you could access other built-in fields (User!ReportName, User!ReportFolder, and User!ExecutionTime, among others), use the same logic to create a larger list of users who would see the default values, or go wild and create your own custom control variable logic using VB code.

One last point about the control parameter: Make sure to push this new variable to the top of the variables list (I wrote about the importance of parameter order here) since other parameters will be dependent upon the value of this one.

Configure the dependent variables

With the control parameter logic in place, let’s look look to the downstream parameter values, where we will conditionally set default values based on whether the former is set to True.

For each parameter we wish to conditionally set, assign a value in the Default Values tab. We’ll use a value expression to test the control parameter value and, if it is True, we’ll assign a specified default value:

As shown, we’ll set a default value of “US” to the Country parameter if the control param is True; otherwise, a null (or Nothing in VB) is assigned. You’ll apply the same pattern to each dependent variable, specifying the conditional value to set for each variable. Note that this still works if you need to assign multiple values (assuming the downstream parameter in question is configured to allow multiple values) by creating an entry for each one and applying the same type of IIF() statement to each one.

Conclusion

This quick tip won’t change your life, but it may save you some time if you routinely deal with report specifications that disallow default parameters, this quick setting can be a time saver during the development and testing cycle.

In a future tip, I’ll expand on the logic I’ve defined here to describe how to build a data-driven parameterization scheme which can provide different default values for different users.

The top 5 reasons to speak at SQL Rally

Tim Mitchell — Fri, 20 Jan 2012 07:15:00 -0600

Have you been thinking about speaking at the upcoming SQL Rally in Dallas? If so, remember that the deadline for submissions is coming up fast – you only have until this Sunday (1/22) to add your abstract to the list of potential sessions.

Why Speak at Rally?

Great question! After all, speaking is hard work and requires a lot of time to prepare. I got the opportunity to speak at last year’s inaugural SQL Rally event, and I am happy to report that it was a great experience.

I want to set you expectations appropriately, so I should dispel any rumors about speaking at this event. To be clear, the following are NOT reasons to present at SQL Rally:

You’ll get a huge speaking fee just for showing up.
A private limousine will chauffeur you everywhere you need to go.
Brent Ozar will see your presentation, and will be so impressed that he’ll invite you to join Brent Ozar PLF<your initial here>.
You'll leave with a huge following of #sqlkaraoke groupies.
You will be appointed to a seat on the PASS board.

Sadly I can’t promise that any of these things will happen simply because you come to Dallas to present at Rally, though your mileage may vary.

I can, however, enumerate some very real expectations that SQL Rally speakers can bank on:

You’ll take your presentations to the next level. Perhaps you’ve been speaking at local events for a while, and maybe even presented at a SQL Saturday event. Do you have your eyes on speaking at national/international events such as the SQL PASS summit, SQL Connections, or SQL Bits? Participating in a regional event such as Rally can help to position you for a run at a larger event.
You’ll get a free pass. Those selected to speak at the event will receive complimentary admission to the event.
You’ll get to know some awesome people. Of course, as a seasoned speaker you already know a lot of people in your area. However, presenting at a larger event such as this exposes you to a wider variety of people, including PASS board members and HQ staff as well as other experienced presenters and chapter leaders. Expanding your network will have an impact on your career (write that down).
You have a good chance of being heckled by local MVP and world-class heckler Sean McCown. Also, SQL for Smarties author Joe Celko is just down the road as well.
You’ll get to hang out in a championship city – with me! Dallas is home to the world champion Mavericks, the American League baseball champion Texas Rangers, and former world champion teams including the Dallas Cowboys and the Dallas Stars. Plus, I’ll be there!

Regardless of which of these motivates you, you won’t be disappointed by coming to Dallas for this year’s SQL Rally. Submit your abstracts today, and I’ll hope to see you there in May!

In 2012, It’s Me and Vanilla Ice

Tim Mitchell — Tue, 27 Dec 2011 07:00:00 -0600

Like many others, I’m planning a few changes for my life for next year. I’m a believer in finding one’s own way, while still abiding the advice of the wise. Often called upon for inspirations are the timeless words of Confucius, the inspiring speeches of Martin Luther King, the controversial yet insightful analyses of Sigmund Freud, and the various authors of the Christian Old Testament book of Psalms. I find value in all of these, but for next year, I’m trying something a little different. I’m going to look a little further off the beaten path to the words of one of the most unforgettable personalities of the twentieth century.

That’s right. I’m invoking the wisdom of Vanilla Ice.

His hair was whack. His clothes were loud. His ink was plentiful. His bass line was ~~stolen~~ catchy. But in the words of his best known contribution to pop culture, I’ve found inspiration for the next twelve months of my life.

In 2012, I’m living out Ice Ice Baby.

All right, stop. Collaborate and listen.

I spent almost two thousand hours working last year – and that’s just my “official” work which doesn’t include after-hours writing projects, travel time, speaking, and professional reading. But as I look back on the year, I can enumerate only a few new things that I learned. Sure, I got incrementally better at the things I already knew, but 2011 is the first year in several that I can’t say that there’s one particular new thing that I’m proud to have truly conquered.

I’ve given this advice to others, but it seems that I’ve failed to follow it this year: Stay on top of your professional development. Don’t just stick with what you now know – branch out and learn. I’ve had a “do” mindset this year, which is noble, but one also has to take pause to learn new things to continue growth.

Looking forward to 2012, there are several things I plan to either a) learn for the first time, or b) take my existing knowledge of that thing to the next level. Among them:

Data Quality Services – there’s not a huge amount of buzz around this yet, but as a business intelligence consultant I see grand potential in this tool. Every organization, without exception, has bad data. I’ve spent very little time getting to know DQS, but I’m planning to dive in to it in 2012, and I expect to be knowledgeable enough to be blogging about it by midyear.
Analysis Services – Although I’ve been working with SSAS for a couple of years now, there are still a few facets of the product that I haven’t yet mastered. I expect to remedy that next year as I explore the product more thoroughly.
Kimball – I use the Kimball data warehouse methodology every day, but there’s so much more left to learn. The Kimball Group has published dozens of books, and I’ve read only parts of a few of them. To a BI professional, this collection is The Bible. I commit to not just own and reference these works, but to consume them with vigor.
Photoshop – Yes, this is completely unrelated to my BI career, but as many of you know, my wife and I started a photography business this year. Although she’s running the business, I want to be in a position to help her out as much as possible. I’ve been using Photoshop (the consumer version) for about a year now, and I have learned enough to perform most basic and a few advanced operations. Toward that objective, I’m acquiring some training materials and am planning a self-guided education to take my postproduction skills to the next level.

Quick to the point, to the point no faking

There’s a dirty word that I keep overusing. I’ve tried not to say it, but it just keeps slipping out. Sometimes I say it subconsciously, and other times I blurt it out in the heat of the moment. As yet, nobody has said it to my face, but I suspect that others may be talking behind my back about my overuse of this word.

The word: Yes.

“Tim, do you want to serve on this committee?” “Yes, I will!”
“Tim, can you proof this white paper?” “Yes, I’ll get right on that.”
“Hey Tim, can you round up and manage some volunteers to help us do this thing?” “Yes, I’m your man.”

Individually, a Yes is a good thing. Helping a friend, leading a team, and participating in a charity event are all great things to agree to. Saying Yes isn’t the enemy. Saying Yes too much, however, can get you in trouble. I’ve been too quick to say Yes to almost every request, and I’m embarrassed to admit that it’s put me in a bind a few times this year.

Saying Yes isn’t just a way to end a conversation – it’s a commitment. When you say Yes to something, you’re telling someone, “Don’t worry about the thing you need done. I’m going to take care of it for you.” It’s a transfer of responsibility. Even if it’s not in writing, it’s a contract. To say Yes with no reasonable possibility of delivering is self-delusion. Faking.

So for 2012, I’m going to continue to say Yes as much as I can, but I’ll do so more cautiously. The three big F’s – family, friends, finances – take priority for my Yes. When opportunities arise that I simply can’t fit in, I’ll have to politely decline. It takes too much effort to pretend that I have more time and energy than I actually do. Life is simply too short to worry about being overloaded, which leads to my next point…

If there was a problem, yo, I’ll solve it.

I have to admit that I wasted a lot of time and mental energy worrying about stuff in 2011. I made some big commitments in this past year and for the most part I’ve delivered (albeit late, in a few cases) on those commitments. However, my tendency to procrastinate was in high gear this year, and I put off until the last minute some tasks that I could have easily checked off my list. This behavior turns into a vicious cycle – procrastination leads to worry when the deadlines start to close in, the worry becomes a distraction that makes it difficult to concentrate on the thing that needs to be done, and the distraction makes it easier to keep procrastinating.

I’m setting a different standard for myself in 2012. If something needs to be done, I’m just going to do it. Is it really that simple? It can be, yes. As an incentive, I’m reclassifying some things that I have previously considered to be entitlements. Watching TV (even my favorite shows), recreational reading, Facebooking, playing Wii, and other similar downtime activities fall into that group; rather than doing these things whenever I choose (or to avoid doing something else), I’m developing a realistic reward system to help balance work with play. By prioritizing the things that need to be done over the things that are enjoyable but not necessarily productive, I expect to reclaim the time I’ve been wasting in worry.

Take heed, 'cause I'm a lyrical poet

I really enjoy writing, and I’m a pretty decent writer. So why don’t I do more of it? Oh, that’s right – I’ve been procrastinating (see previous bullet).

In years past, I’ve been quite an active blogger – I’ve consistently blogged as frequently as once or twice a week. These days, that number is more accurately measured in blogs per month (or even per quarter). That’s simply unacceptable. It’s not that I’m not capable of writing more, nor do I have a lack of material. I’m a consultant – the world is my muse. The only explanation is that I’ve not chosen to make blogging a priority this year.

Now to be fair, I have been busy writing this year – I contributed to the MVP Deep Dives V2 earlier this year, and I’m currently wrapping up another book (SSIS Design Patterns) to be released early next year. However, this is still no excuse to skip out on blogging, which has a faster turnaround, generally requires less effort and research, is more flexible in terms of content, and often reaches a larger readership than book projects. For someone with so much to say, blogging should be a part of my regular routine, not just something that I do when I have spare time.

I’m going to light the fire in 2012 again. I'm setting a goal of one professional blog post per week, with the expectation that I’ll do more than that. I’ve proven that I’m capable of more than that, and by removing the distraction of worry, I haven’t the excuse of not enough time. Further, I’m setting aside weekly time – two hours per, as a start – for blogging, and I’m considering that block of time to be wholly reserved for blogging and not a flexible time slot to catch up on whatever task happens to be overdue at the time.

Word to your mother

In the same vein, I’ve recently revived my personal blog and will continue to share on that forum. In the past, I had posted to my professional blog just about anything, including items that would be of little interest to those who don’t know me personally. Having the separate personal blog allows me to blog more frequently about topics that interest me without annoying the professional audience on my technical blog. Plus, I want to be able to share a piece of myself with my kids when they’re old enough to understand, and I don’t want them to have to try to distill the personal stuff that interests them from the professional stuff that doesn’t.

I haven’t made a hard commitment on a number of personal posts, simply because of the variety of frequency in which a bloggable event occurs. Some might be deep and insightful monologues (I blogged last week about my mom’s house fire back in December of 1980), but others may be quick thoughts about my day. I’ll blog on my personal site as often as appropriate, and I’ll let that play out however it will.

I'm killin' your brain like a poisonous mushroom

My job is literally killing me.

(pause for effect)

I really, really like what I do. I work with great people, I get to solve difficult and interesting problems, and I don’t have to brave the elements while I work. Plus, my job isn’t physically strenuous. And while that last part is a good thing, it’s also a bad thing – it means that I spend almost all of my working hours (sometimes 70-80 of them per week, counting my writing, photo editing, etc.) sitting on my posterior.

Although my numbers (weight/BMI/cholesterol/blood pressure) are reasonable for my age, I’m in the worst shape of my life. I don’t walk enough and hardly ever run. I’ve worked up a sweat maybe four times in the last two months. I deal with a higher-than-average amount of stress. Any M.D. on the planet who read the last two paragraphs would scream at me that I’m headed for really bad stuff in a couple of decades (if not sooner).

There’s only one way to change this. I’ve simply got to get off my can and get active again. I’ve resolved to do this at least a dozen times over the last several years, but the initiative always dies off quickly. In 2012, that all changes. My kids are old enough that we can get out and enjoy physical activities together, even if it’s just playing chase-and-tag in the back yard. There’s a fitness center less than a half mile from my house, where I already have a membership. There are dozens of people in my local and national circle of friends who are involved in fitness initiatives, and many of them are distance runners. I’ve got the motive, means, and opportunity to get in shape – and as of today, I pledge to do so. (Note: I’ll be publishing a separate post on my personal blog with the specifics of my goals.)

Yo man, let's get out of here

The year 2011 is almost done. As you can infer from the above, I’m not altogether happy with my progress this year, and as of now I wash my hands of that old way of thinking. Now that it’s in writing, I’m committed to follow through, with the expectation that both of my loyal blog readers will call me out if I stumble.

And perhaps by this time next year, you’ll all be jealous 'cause I'm out getting mine.

What is #SQLFamily?

Tim Mitchell — Mon, 07 Nov 2011 22:58:37 -0600

There’s a large group of people that mean a lot to me. Some of them I know well, and others I only know peripherally through their relationships with those with whom I am more familiar. Some of them I like a lot, but with others I rarely see eye-to-eye. Many of them I consider to be friends, and a few I would say are close friends, reliable confidantes that I’d trust with my car, my ATM card, my secrets. Most of these people are not geographically close to me, but we make it a point to see each other at least a couple of times per year.

Were it not for the title of this post, you might assume that I’m talking about extended family. But in this case, I’m describing a group of people who are, at times, as close as my own genetics-and-marriage family. This it the SQL community, affectionately known as SQL Family (or #sqlfamily, for you Twitter-er-er-ers).

But what is SQL Family, really? Just a group of people who do a similar job and tolerate each other at conferences? A band of partygoers whose only commonality is an affection for Jaeger bombs and bacon? A strange clique that prevents outsiders from coming in? I’d submit to you that it’s none of these things. Tom LaRock has challenged everyone in the most recent Internet meme to define the following:

What SQL Family means to me:

SQL Family is needing 30 minutes to walk the 150 yards from the hotel entrance to the elevators because I keep bumping into people that I know. It’s knowing that I’ll never ride the escalator without passing a few friends going the other way. It’s the assurance that I’ll never again be that guy in the hotel room at 9pm wishing I was still engaged in the event.

SQL Family is conversing with Bill Graziano and watching him write down what you say in his little notebook. It’s chatting with Joe Webb and knowing that you have his absolute undivided attention. It’s sharing an idea with Andy Leonard and hearing him tell you why it’s a great idea (or why it might not work, but very gently). It’s meeting up with Lori Edwards and feeding off of her passion for community. It’s asking Kevin Kline for his opinion on, well, anything, and getting a very intelligent and insightful reply.

SQL Family is putting a request for help on the wire to #sqlhelp, and immediately getting dozens of replies, direct messages, and code samples from other Family members. It’s an open sharing of ideas without fear of IP theft or plagiarism. It’s an openness to ask any question without getting smacked down. It’s being able to ask questions about DBCC from Paul Randal, the guy who friggin’ wrote DBCC.

SQL Family is being cursed at by Sean McCown and Denny Cherry (and his wife), still knowing that they have your back.

SQL Family is meeting people you’ve “known” for years. It’s learning the real names of people you know only by Twitter handle. It’s sending out a Tweet that you’re arriving in town (almost any town) and getting replies like “How long are you here? Want to meet up?”

SQL Family is the encouragement you get when you present at the PASS Summit for the very first time. It’s 100 people telling you that you’re session is going to be great, and the same 100 people asking afterward how it went. It’s looking out into the audience and seeing not a room full of strangers, but more than just a few friendly faces. It’s a willing presenter on standby ready to fill in at the last minute to avoid an empty slot in the schedule. It’s knowing that someone will loan you their laptop in case yours dies right before your presentation.

SQL Family is getting your picture taken by Pat Wright. It’s a friendly hug from Karla Landrum, and huge bear hugs from both Jes Borland and Erin Stellato. It’s getting to know the (sometimes) non-techie spouses of our SQL Family members. It’s an open invitation to split hotel rooms and travel costs to reduce the cost of attending Family functions. It’s being welcomed into the homes of other Family members.

SQL Family is a line of people cheering on fellow runners as one of their own completes a lengthy run. It’s encouragement and kind words as one of our own battles a serious health issue. It’s sincere condolences when one of us loses a close family member, and hearty congratulations when one of us brings into the world a member of the next generation of the SQL Family.

SQL Family is circling the wagons when one of our own is threatened or treated inappropriately. It’s opening our arms to welcome in new members.

SQL Family is having the ability to disagree or even argue, knowing that, at the end of the day, we’re still a SQL Family.

Don’t Be This Guy

Tim Mitchell — Fri, 02 Sep 2011 07:00:00 -0600

Note: This post was originally posted on October 29, 2009. In the interest of the upcoming conference season, I’m republishing it as a reminder (to myself, as much as anyone) to get off the bench and meet people.

I have a sad story to tell you. Sit down and grab a tissue.

It was 10pm on a cool night in September 2005. Somewhere in Grapevine, Texas, a junior SQL Server professional was sitting alone in a hotel room watching TV. He was tired but not exhausted, having spent all day learning his trade at the Super Bowl of SQL Server conventions, the annual PASS Summit. Although he had met a few people, he didn’t really get to know them or try to meet up with anyone outside the normal conference hours. He attended a couple of parties, but left early and didn’t get beyond chit-chat with others.

Across town, much fun was being had. Stories were told, laughs were shared, and personalities bonded. People went out on a limb and introduced themselves to others they’d never met. Some people would literally change the trajectories of their careers through the relationships that were built at this conference. Sadly, the guy in the hotel across town is missing out on all of this.

As the conference wore on, he saw all those people chatting between sessions and at dinner, laughing and getting to know each other, and secretly he wished to be connected to some other professionals. You see, since he was the only SQL Server professional at his place of employment, he didn’t have a lot of opportunities to talk shop in person with others. He longed for what they had, but couldn’t find the initiative to start up meaningful conversations with others.

As the conference wrapped up at the end of the week, he was appreciative of the technical knowledge he’d be taking home, but couldn’t stop dwelling on the fact that he’d done little networking at this event. It was almost as if he’d missed out on half of the conference.

The man goes back to his job and reads the blogs of those who also attended the summit. He begins to think, “I’m no different than those people, I just need to be more assertive.” He realizes that networking is as big a part of career success as is technical knowledge, and that it’s easier than he’s made it out to be in his mind. He vows then never to again sit on the sidelines; he promises to himself that he will take full advantage of these functions by getting involved in related events outside the scope of the conference.

The story does have a happy ending. “That guy” was me, and I did indeed waste a huge networking opportunity ~~four~~ six short years ago. With that lesson in mind, I swore off being the wallflower and now take the initiative to be more assertive at each technical event I attend. Though I don’t set specific numeric goals, I make it a primary mission to get connected with people, to learn what they do and to share a little about what I do. I’ve come to learn that getting to know fellow SQL Server professionals at technical conferences is at least as important – and quite possibly even more so – as the technical content. I can tell you firsthand that the relationships I’ve built since then have led to many opportunities in my career I wouldn’t have otherwise found, and I’ve built some friendships along the way as well.

So the takeaway is, don’t be me – at least the Me In 2005. Don’t be lonely hotel room guy: use your hotel room for one thing – sleep – and spend the time with others getting to know them. At next week’s PASS Summit, there are official events scheduled for every night of the conference, along with numerous unofficial events. There are vendor breakfast presentations, lunchtime meet-and-greets, and various other opportunities to press flesh and get to know your fellow SQL Server professionals. One of the people you meet could be your next boss, employee, business partner, client, or even a good friend.

2011 Addendum: Since I wrote this post almost two years ago, a great deal has transpired in my life and my career in particular. I’ve made a lot of new friends in the SQL Server community, changed jobs to go to work for an outstanding consulting company, and was twice awarded as a Microsoft MVP. I can safely say that all of these things are directly related to building relationships and contributing to the community. Getting engaged with others isn’t just a feel-good activity; it can help you find ways to advance in your career.

SQL Saturday #90 OKC

Tim Mitchell — Mon, 29 Aug 2011 07:45:00 -0600

This past Saturday, I made the relatively short (3 hour) drive north to Oklahoma City to participate in their first ever SQL Saturday event. I have to say that this was one of the best SQL Saturday events I’ve attended, which is especially notable since it was their first such event. The venue they chose was the Moore Norman Technology Center, which was well suited for the site and layout of this event. The big room used for the opening keynote by Steve Jones was subdivided (using remote-controlled motorized room dividers – very cool!) into the three rooms required for the three tracks. Although one of the rooms had some audio and video issues, the facilities were more than adequate, and building staff were on hand to help out with the few issues that came up. Lots of parking, wide hallways, and plenty of restrooms made the facility easy to navigate.

This SQL Saturday had just three tracks, the smallest number of any such event I’ve attended. It worked out well, though – the tracks were broken out by discipline (DBA, dev, BI) so each timeslot offered a variety of topics.

I got to present two sessions in the BI track: I did my T-SQL vs. SSIS session that I originally wrote for SQL Rally, and I also offered an introductory BI session that briefly covered SSIS, SSAS, and SSRS. Each of my sessions was well attended – 48 in the first, and 66 in the second – with lots of audience interaction. By the way, my slide decks and code samples are available on the Presentation Notes page of my website.

The OKC group hosted a speaker dinner the night before, as well as an attendee reception after the event on Saturday. Unfortunately, the notification about the attendee reception was sent out late during the week before the event, and as a result there wasn’t a big turnout on Saturday. However, a good number of speakers showed up on Friday night. The OKC group was kind enough to buy a generous round of appetizers both nights.

This was a well organized event, so I don’t have much to critique. Here are a few observations I made:

Signage was lacking. Although the building was easy to find using GPS, I didn’t see any signs at all pointing to the building, which entrance to use, etc. Fortunately, it was not a huge building, and even though I entered through the wrong door I was able to quickly find my way.
Volunteer shirts – I was asked a lot of questions about logistics and such (where are the restrooms? What time is session <X>?) simply because I had a speaker shirt. I like having the event staff wearing different colored shirts so they can quickly be identified as someone in the know.
Communication. As I mentioned, the word about the reception went out late last week. Even though it was branded as an “unofficial” event, I think there would have been a better turnout had the message gone out sooner.

Apart from those issues, I thought the event went very well. Hats off to Matthew Brimer, Kristin Ferrier, and the rest of the Oklahoma City SQL crew for hosting an excellent event.

As an aside, we had a significant representation from the Dallas area at this event. I didn’t get an exact count, but there were at least 10 speakers from the Dallas area, and at least another 10 attendees. Among those in attendance:

Ryan Adams (with whom I shared a car and room)
Russ Loski
Greg Galloway (my colleague at Artis Consulting)
Sri Sridharan
Sean & Jen McCown
Paul Hunter
Shawn Weisfeld

Thanks again to the OKC crew for having us! Congrats on a great event, and we look forward to it again next year.

T-SQL Tuesday (er, Wednesday): Crap Code

Tim Mitchell — Wed, 10 Aug 2011 07:30:00 -0600

Ok, I have two admissions that I must bare to the world in this post. The first is that I’ve been a little lazy. I’ve admired – from a distance – this T-SQL Tuesday thing ever since it started. It’s been great! A bunch of people blogging on the same day about their own interpretation of some common topic – brilliant! Sadly, I’ve been ~~too busy playing Angry Birds~~ keeping up with my other responsibilities and haven’t made the time to add my own submissions to this phenomenon. However, I’ve finally ~~killed the last of those damned pigs~~ caught up on my other work, so I get to let my hair down for a bit and join the rest of you fine folks in this thing.

The second admission is that I’ve rolled out some, uh, less than optimal solutions in the past. Any developer who is truthful will admit to a few instances where we’ve rolled out solutions with poorly performing cursors, improperly indexed tables, and convoluted code, all accompanied by little if any documentation – quite simply, it happens from time to time. But in the interest of the T-SQL Tuesday topic for this month, participants are asked to share about a time when they rolled out some truly…

Crap Code

Without a doubt, I could find a number of instances where I’ve written code that wasn’t worth the electricity it took to store it, but one such occasion really sticks out. This was early in my IT career, close to 10 years and several jobs ago, and I was writing my first real production database application. I fancied myself a bit of an expert – after all, I was completing my first relational database theory class in college, and I had a solid 18 months experience in SQL Server administration.

I had talked my boss into letting me architect (!?!) a solution that would allow educators to create short web-based assessments, to replace the manual process (as in literal cut and paste – with scissors and glue) they were currently using. With the requisite approval in hand, I began my discovery. Yes, me – not only was I the architect, I was also the business analyst, developer, tester, and system admin. #winning

The Development

In meeting with the educators, I learned that the assessments would be very brief – at most, 10 questions each. Educators would be able to share assessments with others, but could only edit or delete their own. The final product would allow the educators to save to PDF and print the forms – there was no interest in actually delivering the exams online, which made things a bit easier for me.

So as I set out into the design phase, I envisioned the entities I would need. Since the number of questions would be very limited, I decided to use a single table to store the questions directly with the assessments. (Don’t get ahead of me here – it gets better.) I did manage to store the answers to the questions in a separate table – not because it was a best practice, but simply because I couldn’t figure out an easy way to squeeze them into dbo.InsanelyWideAssessmentAndQuestionTable. Using my freshly minted ASP.NET skills – I had recently read two entire books about C# on ASP.NET – I started coding the front end. A simple yet ugly interface, rudimentary file upload capabilities, and slow response time, but during my solo testing sessions, it did manage to do what I intended.

The Deployment

I’m certain I violated every test and deployment best practice ever written. Source code consisted of a bunch of .zip files created at irregular intervals, and parallel testing involved two laptops on my desk, connecting to the web/database server under my desk. Deployment was the easiest part – I just manually copied the web application files to the web server and restored the database from my desktop dev machine to the server as soon as everything seemed to function without errors. What could go wrong?

The Meltdown

Did I mention that I was still in college at the time? Two mornings a week, I drove to university about an hour away. I had deployed the whole thing about 9pm the night before and e-mailed the instructions for system use to the key personnel. It was no surprise that, in the middle of class the next morning, my cell started ringing. We’ve got about 100 assessments in the system, the voice said, but we just discovered that the answers are associated to the wrong assessments! Further, as the educators enter the data into the system, their entry was often associated with someone else, so they couldn’t go back and edit or delete it.

After clearing 60 miles of 2-lane road back to my office in record time, I started some very quick triage while trying to avoid a few dozen dirty looks from my users. The problem, at least the main one, was that I was using incorrectly scoped variables to track user IDs in the ASP.NET application, which caused the assessments to be associated with the last user who had saved any item, and with more than 20 people entering data, there were more wrong than right. Further, since tests/questions and answers were entered in two different steps, most the answers were also incorrectly linked.

In true rookie fashion, I tried feverishly to fix the error on the fly. Now stationed in the midst of the users trying to enter data, I would stand up every 15 minutes or so and announce, “I’m recompiling, please log off!”. This went on for maybe 90 minutes, at which point I – in probably the only wise decision I made that day – stopped the bloodshed and asked that we reconvene later after we were able to correct the development issues.

The Aftermath

I was ready to fall on my sword over the debacle. After all, it was me and my ego that had caused the whole mess. Fortunately, I worked in a flexible environment that allowed us to introduce reasonable risk, even if it meant the occasional failure. Along the same lines, I was given the time to make it right: after two rounds of much more rigorous testing, I successfully deployed the updated (and this time, properly functioning) application several weeks later. Still, despite the eventual positive ending, I was embarrassed to say the least, and lost a little bit of cred that day.

The Lesson

Was it ever. I learned some hard lessons that day, lessons that I carry still to this day.

How did it change me? Let me count the ways:

Database normalization. Learn it, live it.
Don’t be afraid to admit when you’re over your head, even if it’s late in the process.
Test, test, test. A successful compilation isn’t the end of testing – it’s just the beginning.
Testing should involve business users, not just technical staff, and should simulate realistic usage as much as possible.
Never implement anything that you can’t be there to support on the day of go-live.
Don’t rush deployment. Missed deadlines will be forgotten, but crappy applications can live on forever.

And the most important lesson I learned that day:

Mistakes are a part of life, but you must a) own your mistakes and b) learn from them.

To that end, the catastrophic deployment described here was actually one of the highlights of my career. Never before or since has a single incident taught me so much.

Can You Endorse Me?

Tim Mitchell — Wed, 20 Jul 2011 07:15:00 -0600

“Hey man, this is Ty*, what’s happening?” roared the familiar voice in my Bluetooth earpiece. It was a voice I knew well, and briefly brought a smile to my face. After exchanging pleasantries and catching up a bit, Ty revealed the real reason for his call: “Say, dude, I’m looking for a new job, and I was hoping you’d write me a letter of recommendation.”

In the past, Ty and I had known each other reasonably well, having spent a good deal of time working shoulder-to-shoulder on some very difficult projects many years back. Although our careers had taken different directions and led us to different geographical areas, we had loosely kept in touch over time, so I had a rough idea of where Ty was in his career (and vice versa). I knew him to be an entertaining and interesting guy, never without a story to tell to help pass the time. Unfortunately, I also knew his professional demeanor: he was frequently bitter for no good reason, talked sharply to end users he supported (reaching outright beratement on a couple of occasions), and was largely unmotivated to do any more than was absolutely required.

Lending a Hand

I love to see people do well in their careers. Specifically, I like to see eager newbies get engaged in something that they’re passionate about, and progressively work their way into senior-level (or even management) roles. Just as enjoyable is to observe a seasoned professional make a career change that allows them to use their years of experience in different ways.

I’ve had the privilege of helping a small number of people in their journey of career progression. Whether it was lending my experience in resume preparation, offering to provide a job reference or letter of recommendation, or just offering counsel to them as they prepared for a job change, I was honored to help out fellow professionals in whom I was a lot of potential. After all, I was the beneficiary of much of the same kind of help as I was getting started, an was glad to have the opportunity to pay it forward.

It’s a Small World, After All

So it’s a no-brainer when one is presented with the opportunity to help out someone with obvious potential. Things get a little trickier when dealing with someone with whom you have a friendship when you have observed that person demonstrate a significant lack of ability and/or professionalism. On one hand, it’s natural to want to help out a friend even if you have reservations about their suitability. However, the world – and the SQL Server community in particular - is very small. The last thing you want to do is to put your stamp on someone (friend or otherwise) whom you know to have serious career-limiting issues. After all, it’s bad for you, bad for their future employer/client/partner, and inevitably is probably bad for your friend as well.

Let ‘Em Down Easy

So how do you turn down a request for a reference/recommendation while preserving the relationship? It’s not an easy thing to do, mind you. The exact response depends on how strong a relationship you have with the person, whether or not you value that relationship, and how open the person is to critical feedback.

There are several different things you can do to avoid a blunt refusal of a questionable request for endorsement:

Plead ignorance. Has it been many years since you worked with this person? If a significant amount of time has passed since you knew him professionally, you could politely decline citing that you don’t feel comfortable giving a recommendation because of the length of time since you’ve observed his work habits and skills. Over time, people can change: a motivated worker could become burned out, and conversely, an immature employee could see the error of his ways and get straight. If it’s been many years since you’ve dealt with him, it’s probably not fair to assume that he’s still at either end of the spectrum, whether he was a superstar or slacker.
Shoot straight. Do you value her friendship and want to see her improve? Is your relationship such that you can give her honest feedback without setting her off? If the reason you hesitate to offer your endorsement is an attribute that someone could reasonably be expected to change, level with her. Tell her the potential you see in her, but that you’ve got some reservations about putting your name on the line for her. This approach is not without risks, so proceed with caution. However, for the right person, this might be just the right thing to put her on the right path to a successful career.
Help in other ways. Remember, he is asking for your endorsement. It’s not a demand; you have the right to say no. You could simply reply that you don’t feel comfortable offering your recommendation, but offer to help him in other ways. Does his resume need a touch-up? Maybe he could use a mock interview to prepare for the job hunt? Is he lacking a specific technical skill that you have? There are many ways to help that don’t involve a formal recommendation, and one of these could allow you to provide assistance to your friend without risking your own street cred to do so.

Conclusion

In my case, I simply declined Ty’s request for a recommendation letter. It had been a long while since we worked together, I replied to him, and I didn’t know his current skillset or demeanor and wouldn’t feel comfortable offering my recommendation. Had we been closer friends, I’d likely have worked with him to help him understand the concerns that I had about his demeanor. But in this case, I just politely declined and left it at that.

So how do you handle these cases? Have you had to turn down a friend that requested your endorsement? How did you handle it?

* Not his real name

SQL Saturday Oklahoma City

Tim Mitchell — Fri, 17 Jun 2011 07:11:00 -0600

Attention, southerners (or those who can find their way south)! There’s another great SQL Saturday event coming up in August in Oklahoma City, and I hope to see you there. The Oklahoma City SQL Server User Group is hosting this event on August 27th. Our group in Dallas has been privileged to have the OKC folks so close to us, and they’ve come down and supported our SQL Saturday events over the past couple of years – it’s a great group of people up there.

According to their speaker page, they’re doing several 60 minute sessions as well as one 30 minute session per track. As far as I know, the 30 minute track hasn’t been used at a prior SQL Saturday event so I’m looking forward to seeing how they creatively fill that time slot. Maybe even a lightning round….?

I’m planning to submit a couple of sessions – I’m doing one Intro to BI session, and I’ll probably try to work in my T-SQL vs. SSIS session from SQL Rally as well.

The call for speakers is open for another 10 days or so, so if you’re interested in speaking at this event, please visit the Speakers page and submit your session. If you’re in the Dallas area and are planning to attend (either as a speaker or attendee), please send me a message – I’m coordinating with everyone we know of who is going from this area to try to arrange ride and accommodation sharing.

See you there!

SQL Rally, PASS Summit, and More

Tim Mitchell — Thu, 16 Jun 2011 07:50:00 -0600

It’s been a busy couple of weeks for SQL Server community and PASS related news.

First and probably most importantly is that the location for SQL Rally 2012 has been chosen, and we are happy to announce that it’ll be right here in Dallas! After several months of research and evaluation, PASS awarded next year’s event to this area based largely on the capacity and pricing of local convention centers. Our local group, the North Texas SQL Server User Group, is honored to have Dallas chosen as the host city, and we’ve got dozens of volunteers at the ready to help make this an exceptional event.

We’ve also learned that the PASS Summit will be moved out of Seattle in 2013, and will be hosted in Charlotte, North Carolina. Congratulations to Peter Shire and the rest of the cast and crew at the Charlotte user group! Dallas was also in the running to host this event, and we’re still hopeful that we’ll get the opportunity to again host the Summit (it was last held in this area in 2005) sometime soon.

On a personal note, I learned a few weeks ago that I’ll be delivering a preconference session at the PASS Summit in October. Along with Andy Leonard and Matt Masson, I’ll spend a day talking about SSIS in the Enterprise, covering in detail my favorite BI-related topic. If you’re not already planning to take in a precon at the Summit this year, we’d love to have you join us for a day in the life of SSIS. There might even be bacon.

Along the same lines, I received notification yesterday that one of my PASS program sessions was selected, and I’ll be doing a regular session in addition to the Monday precon. I’ll be discussing some of the advanced transformations in SSIS that I don’t normally get the opportunity to show. This is my first time to present a regular session at the Summit, so I’m very much looking forward to this opportunity.