Ward Pond's SQL Server blog

Database Programming: The Time Zone Conversion Beast, Once Comatose, Returns to Taunt Us a Second Time (or, An Answer for Iain)

Now go away, or we shall taunt you a second time.
     - John Cleese as a French knight (picture at left); Monty Python and the Holy Grail

One of the reasons I started blogging over four years ago was my conviction that the coding challenges with which my employer was presenting me, while stimulating and challenging, were far from unique.  I hate to say “I told you so,” but then I didn’t tell you, I told me, so it would be both pointless and wrong to say “I told you so.”  But, “I told me so” doesn’t sound right either.

So it’s probably just best to move on.

The point of this little ramble is that I get an extra little jolt of satisfaction from this exercise when I get questions from readers who are trying to adapt to suit their needs the code presented in an old post.  It’s even more fun when a question prompts me to expand upon a design to fulfill an unanticipated need.

So it is with Iain Johnson’s comments on a post that’s over three years old: Database Programming: Slaying The Time Zone Conversion Beast (or at least rendering it comatose).  Maybe he found it because my buddy Jimmy May gave it some link love lately.  Perhaps he’s one of dozens(!) of folks who find their way here every day via one of the search engines.

Wherever Iain came from, he’s got a very interesting requirement:

… for a global helpdesk with regional centres… I need to calculate two SLA times for a call - the time by when the caller should have been contacted about their issue (typically within an hour) and the time by which the problem should be solved for them (typically within 2 working days).

The calculation needs to take account of working hours (so a call logged 30 minutes before the helpdesk closes for the day, should be 'contacted' within 30 minutes of the helpdesk opening on it's next working day. I also need to take account of public holidays, i.e. non-working days, other than weekends.

Well, I love stuff like this.. and what ends up being the coolest thing of all is that, once the proper data are in place, we can write this query in one glorified T-SQL statement.

I’ve attached a little proof-of-concept to this post.  You’ll find a read-me document as well as two SQL scripts.  In order to keep this post from getting unwieldy, I’ll put my commentary in the read-me.  Purists should note that this new solution makes use of a recursive common table expression, so while our previous conversations were applicable to SQL Server 2000, this solution will only work on SQL Server 2005 and above.  I’ve tested it on SQL Server 2005 SP3 and SQL Server 2008 SP1.

The code isn’t perfect, but it’s pretty interesting.  Thanks to Iain for his thought-provoking question, and his otherworldly patience in awaiting my answer.

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Serving the SQL Server Blogosphere for a Twenty-Fifth of a Century!

Yes, it’s true.. when this post goes live, it will have been exactly four years (adjusted for a time zone differential.. how apropos) since this humble corner of the InterWebs was available for instantiation in your browser of choice.

As I’ve done each year since, I celebrate this occasion by sincerely thanking you for your visits, as well as your questions and comments.  What’s especially gratifying of late is the amount of dialog transpiring over posts that are two or three years old.  I had hoped that these posts would have a shelf life, and I greatly appreciate your continuing interest in them..  as well as the new ones!

We’ve made it together an entire American presidential term..  care to join me in the “drive for five”?

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

SQL Rap Contest Results in the Hizzle!

As my team celebrates the successful release of the latest version (v 2.5, for those of you keeping score at home) of the SQLRAP (SQL Risk Assessment Program) toolset, it should also be noted that Michelle Ufford has announced the winner of her SQL Rap Contest.

You can read about how this whole thing got started here, here, here, and here..  suffice it to say that Michelle, Brent Ozar, and I were privileged to judge nineteen very creative entries.

If you’re interested in the results, head on over to Michelle’s post and discover who took home the prize..

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Database Programming: Using the .modify() Method Against an XML Variable in SQL Server 2008

Make a note..  here’s my first useful programming tip for SQL Server 2008 (and yes, there will be more to come; I’m about a week and a half into my first SQL Server 2008 development project)..

Way back in February of aught-seven, I shared Jerry’s solution for running the .modify() method against an XML memory variable, which wasn’t possible in SQL Server 2005.  Jerry built a helper procedure and used it to build and execute the XQuery.

Well, as nice as it was to have Jerry’s workaround in our quiver, imagine how wonderful I felt when SQL Server 2008 very politely dealt with the following directive:

-- add the Request node to the XML
SET @IncomingXML.modify('insert <Request UserName="{sql:variable ("@UserName")}">
</Request> as first into /IPNuggets[1])[1]')

This syntax takes XML which looks like this..

<IPNuggets>
  <IPNugget Version="6" Type="1">
    <NVarCharValue>A Value</NVarCharValue>
  </IPNugget>
  <IPNugget Version="6" Type="2">
    <NVarCharValue>Another Value</NVarCharValue>
  </IPNugget>
</IPNuggeta>

..and makes it look like this..

In this case, we’re adding the Request node to the XML, as the first child of the first (and only) /IPNuggets link, and we’re assigning the contents of the T-SQL variable @UserName (in this case, “DOMAIN\YourNameHere”) to the UserName element.

As helpful as Jerry’s workaround has been, I like this way better..  kudos to the SQL Server 2008 development team for repairing this issue!

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Happy Birthday, Mom v78.04

Today would’ve been my mother’s 82nd birthday if we hadn’t lost her to emphysema on 4 November 2004.  I wish I could call her today, but I can’t.

I know Father’s Day is coming up this weekend.  Dads are important, but just the same, if you’ve got the opportunity, throw some love and attention towards your mother as well.

If my experience is any guide, a day will come where you’ll regard as a missed opportunity every day you didn’t.

Happy birthday, Mom.  I love you and miss you very much.

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

It’s Milestone Season!

June is definitely milestone season in this neck of the woods.

Not only is today the third anniversary of my start with the ITOE Technology Architects group (the artists formerly known as the SQL CoE), but this past Sunday marked my tenth(!) anniversary as a full-time employee at Microsoft.  Throw in the end of school last week, the blog’s anniversary on the 22nd, and my wedding anniversary in the middle of July, and there is much to commemorate and celebrate at this time of year.

Despite recent challenges, blessings continue in abundance..  just look at the milestones!

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Mark Russinovich Windows 7 Application Compatibility Virtual Roundtable on June 18

On Thursday, June 18 at 11am PDT, Mark Russinovich will host the first of a two-session virtual roundtable for IT pros worldwide to discuss Windows 7 Application Compatibility. This is a chance for IT pros to learn all about the Windows 7 Application Compatibility and answer any deployment and management questions that they may have. As part of the “virtual” experience, IT pros will be able to submit their questions about Windows 7 Application Compatibility to the panel live during the event—or submit questions in advance to vrtable@microsoft.com.

So..  if you want to get up to speed on application compatibility in Windows 7, save the date now and plan to join Mark next Thursday, June 18 at 11am PDT at https://ms.istreamplanet.com/springboard.  I’ve attached a .ics file to the post to make reminding yourself even easier.

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Another Period of Blog Neglect Ends; Firefox Display Issues Repaired

Earlier this evening, thanks to a helpful comment from eXcalibur, I was able to resolve the funky display issues which have plagued Firefox-based visitors since I started playing with CSS overrides in early May.  Thanks, eXcalibur, for the sage advice.

I spent the first half of May working pretty hard on a couple of projects (SQL RAP 2.5, and an internal application called SIPA) before I suffered a relapse of my digestive maladies mid-month.  A couple of short hospitalizations later, I’ve been back to work since the beginning of the week and am starting to feel almost fully human.

So..  if you perchance noticed I was missing in action, I’m back; if that had slipped your notice, take heart in the fact that you’re most certainly not alone.

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

More Blog Disruptions

I'm tweaking CSS in an effort to resolve the FireFox display issues.  I'll update when this work is complete.

8:30p UPDATE: I've put everything back the way it was earlier today.  The FireFox issue remains unresolved.

     -wp

Blog Disruptions Today Now Complete; New Toys Now Available

UPDATED 3 May 2009 5:00p ET: My updates are complete for the day.

I’ve widened the left-hand sidebar a pixel or three to accommodate two new toys, both of which may be found in the revamped News section:

• a little Flash app that shows my latest Twitter tweets; and
• another application that shows the latest from Microsoft TechNet, the kind hosts of this little corner of the Internets.  In addition to a daily news feed, this widget is a gateway to all sorts of other TechNet content:
• Videos
• Podcasts
• content from TechNet magazine
• upcoming webcasts
• on-demand webcasts

Note that there’s a Get & Share link at the bottom of the TechNet widget, which allows you to share content from the widget on many popular social networking services, including Twitter and Facebook, as well as Live, delicious, StumbleUpon, and others.

Please let me know if you have any positive or negative comments or experiences with the new layout and features!

-wp 

I’m playing around with features and formatting this afternoon.  Those viewing via HTML may notice some disruptions.

I’ll update this post when my tweaking is complete.

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Thank You, Chris and Jorge: Follow-Up to Chris Shaw’s SQL Quiz 4

Last week I was tagged by Jorge Segarra (a/k/a @SQLChicken) in a meme started by Chris Shaw, who asked a great question about leaders in one’s career.  In reflecting on my response, it occurred to me that there was only one of the people I listed who it was possible for me to be in touch with, but wasn’t.  I even remarked on it in the post:

I haven’t spoken with John [Stier] in 25 years, and that makes me sad.

Well, we live in the Information Age, where happy endings are possible for certain episodes of sadness brought on by ignorance.  I surfed out to the Stony Brook University website and discovered that my first professional mentor is still working there, and still in the tech arena.

I sent him and email and he replied less than two hours later.  I’m back in touch with John, and it looks like he and my father have a chance to renew their acquaintance as well.

So if you’re ever wondering if anything good can flow from participating in a meme, here’s one data point in their support.  Thanks, Chris and Jorge, for motivating me to do something that I clearly should’ve done a long time ago!

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Paul Randal Explains a Performance Bug

Paul Randal has another must-read post regarding a performance bug which surfaces in earlier versions of SQL Server 2005 and SQL Server 2008 when NOLOCK scans are executed against tables with off-row LOB data.  Both the causal scenario and the nature of the fix are clearly discussed.

Get your geek beanies a-twirlin’ and check out Paul’s post.

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Yes, It’s True – I’m A Judge in SQL Fool’s RAP Contest

I’ll say one thing for Michelle Ufford, the SQL Fool: she’s not one to leave a good idea on the table, unseized.

Yesterday she and I indulged in a little bit of silliness after I made an innocent tweet about some Risk Assessment Plan work I was doing.  Well, the SQL Server Twitter flock got hold of it and there was quite a bit of conversation around it during the day today.

And then Michelle had an inspiration, which you can read about here.

In a nutshell, Michelle is having a SQL RAP contest.  She’s accepting entries until 8pm CT on Monday, 18 May 2009.  Then Michelle, Brent Ozar (he of the Hulk Hands), and I will review the submissions and pick a winner.

Yes, a folk singer/songwriter and blues guitarist is judging a RAP contest.  Bear two things in mind:

1. It’s a SQL RAP contest
2. There’s no entry fee, so in this case you’re definitely getting what you pay for

Despite the rank incompetence of at least one judge, there’s even a prize – a nifty little electronic gadget.  You can get a full description of the prize and complete details of the contest in Michelle’s announcement.

You’ve got a little less than three weeks..  plenty of time to bring da noize, bring da funk, seeing as how Michelle and I started this mess in the course of an evening.

Let’s see what you’ve got!

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

So.. *This* Is How Twitter Gets People Into Trouble..

Consider this post a parable about what can happen if you let Twitter into your life..

I spent part of my day researching a particular capability of the TechSmith Camtasia Studio software my colleague Venkata Raj Pochiraju has been using to record readiness materials for our upcoming SQLRAP 2.5 release.  I made what I thought to be an innocent tweet regarding this activity:

sqltwit playing with Camtasia for building SQLRAP readiness content.. pretty interesting stuff..

Imagine my surprise to find this in my TweetDeck a couple of hours later:

sqlfool @sqltwit so here it is... Ward's RAP: http://sqlfool.com/2009/04/wards-rap.

Well, you definitely need to click the link and read Michelle’s “Ward’s RAP.”

It’s funny.  Actually, it’s hysterical.

So hysterical it cannot go unanswered.

In perhaps my most dubious use of company bandwidth yet, I hereby present, with apologies to.. well..  everyone..

Michelle’s RAP

Yo yo yo..  correlated wait stats in the house!

My name’s Michelle Ufford, and I’m so cool
I self-identify as “The SQL Fool”
System table posters I’ve been known to hoard
And I still design schema on a dry-erase board
I normalize and optimize and make a decent living
I recompile on Arbor Day and DBCC on Thanksgiving
I’m the one fool to call when you’re fragmented or unclustered
I’ll bring processes and practices that really cut the mustard

Yo yo yo..  I found a new DEE EMM VEEEEEE!
(TEE HEE HEEEE (Tee Hee Hee (tee hee hee))
NEW DEE EMM VEEEEEE!)

Without feeling discomforted or ill at ease
I make public spectacle of my expertise
I blog and I Facebook and I speak and I tweet
And I drop Caucasian rap with a stuttering beat
I’ve presented at the Iowa Camp for Code
I’ve got an abstract in at PASS about Super Bowl load
I’ve even done a Webcast for the Pain-of-the-Week
Face it, all you posers!  I’m the über-she-geek!

Yo yo yo..  have you heard this SQL 2008 noise?
There’s new data types in here, man!
Hierarchical?  Not farcical..
Filestream?  A she-geek’s dreeeeaaaaam!

So I blogged and I tweeted and I generally took lightly
A fine product offering that Ward works on daily and nightly
The SQL Risk Assessment Program is that of which I speak
(A SQL RAP will help your instances run at their def peak)
I exploited the homonym of the acronym and the rap
To give a fellow Twitterato a bit of good-natured crap
Now I’ve learned my lesson; I hit a nuke with rubber bands
I fired my rappin’ pop-gun; now Armageddon’s on my hands!

Yo yo yo..  I’ve got a broken forwarding chain here, man..
I need that DEE EMM VEEEEEE!
(TEE HEE HEEEE (Tee Hee Hee (tee hee hee))
NEW DEE EMM VEEEEEE!)

Aw man!  It’s in master.
I got stuff to do.
I’m out, man.

So..  this is how Twitter gets people into trouble..

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Chris Shaw’s SQL Quiz 4

UPDATED 25 April 2009 to fix the link to Jimmy’s blog

As a consequence of my recent ascension to Twitterato-hood (follow me @sqltwit), I’ve made several new SQL friends over the last couple of weeks.  One of those is Jorge Segarra (a/k/a @SQLChicken in the Twitterverse), a SQL DBA and Systems administrator with University Community Hospital in Tampa.  Here in the woods, that makes us neighbors.

Jorge recently tagged me in a meme started by Chris Shaw, who asks..

Who has been a great leader in your career and what made them a great leader?

This is a great question.  Jorge notes in his discussion that, at 26, he hasn’t had much time to evaluate leadership styles.  Well, I’ve had the great fortune to work for a number of great leaders over the last (gulp) 30 years (yes, Jorge, any older siblings you might have were likely gleams in your parents’ eyes when I got started):

• Joachim “John” Stier, my first boss in the computer business, had me forced on him by nepotism but never treated me that way.  He taught me what I needed to know and set ambitious but achievable goals, I learned and applied, and in the end I completed a 437-FORTRAN-component conversion project in eight weeks when I didn’t know FORTRAN six days before I started.  John taught me that I didn’t need my dad’s help to get a job in this business.  I haven’t spoken with John in 25 years, and that makes me sad.
• Gary London was my second boss in the computer business, and he proved to me that I didn’t need my dad’s help to get a job.  He also taught me the value of making and executing a plan rather than working randomly.  He and I did some pretty amazing things on an Apple IIe with a DRM CP/M card.
• Matthew Sargent, my third boss in the computer business, ran a consulting shop I worked in for three years in Los Angeles.  He taught me the value of communicating with non-technical people in plain English, a skill he possessed in spades.  Matthew also gave me my first opportunity to manage people in a technical environment, an experience which renders a second opportunity unpursued.  Matthew passed in the late ‘80s and is greatly missed by all who knew and worked with him.
• Wes Contryman was one of my Project Managers during my tenure at the Los Angeles-based oil company I worked at for fifteen years.  Wes taught me that it was fun to take on big and/or sensitive projects, and make them succeed in creative ways.  In Wes’ world, playing it safe was inconceivable, and the day was incomplete unless three co-workers groaned from his puns (he once pulled off a three-level, bilingual pun that must’ve taken him a week to put together).  Wes taught me that the key to being a happy, successful geek was to be a happy geek who was comfortable in his own skin.  Towards that end he allowed me to drive his Triumph TR7 several times, which rekindled my own passion for British clunkers roadsters.  Wes passed in 1991 from pancreatic cancer and I cried like a baby.  My son Zach was born less than 18 months later; one of his middle names is Wesley.
• Jim Higgins was a director in the oil company who fostered my transfer from Los Angeles to Tulsa (that may not sound like a favor, but it set in motion the chain of events which led me to Microsoft, so we’ll give Jim a mulligan on the Tulsa thing).  Jim and I first crossed paths when we were working on the same project despite the fact that he worked in a division in Buffalo and I worked at Corporate in Los Angeles.  Jim made an incredible effort to stay in touch with me over a number of years, and his rising path led him to a position in Tulsa where he could hire a guy like me, which he did.  Jim’s greatest gift is his ability to take a room full of people ready to choke each other and lead them to consensus in such a manner that everyone in the room thinks it was their idea.  Those of us on staff saw this so often we called it, “The Higgins Effect.”
• Brian Shive gives Wes Contryman a run for “Most Brilliant Technician Who’s Ever Managed Me.”  After working with Codd and Dade early in his career, he was “lucky” enough to be my manager at a time when my reputation was at something of a low ebb.  He didn’t believe the gossip about me, but rather treated me as a capable human being who wanted to do a good job.  Brian is one of the people who’s directly responsible for the fact that I’m approaching my 10-year anniversary at Microsoft (and he will have to live with that guilt *g*).  Candidates from the first four MCA:Database rotations will recall my homage to Brian in the classes’ data modeling exercises: the client was the Shive Community Music School.
• Mark Pohto was the best I’ve ever seen at letting senior technical people run on a long leash.  Of course, as a senior technical guy himself, it would make sense that he’d understand how to do that..  but many senior technical people who become managers seem to forget the mindsets that got them there.  Mark remembered so well that his passion drew him back to the technical side, which is good for him but sad for senior technical people craving compassionate management.
• Tim Wolff managed the transition from colleague to manager better than any of the many colleagues I’ve watched get “promoted” over me (no bitterness here whatsoever.. remember, Matthew Sargent taught me twenty-five years ago that I don’t want direct reports).  He does what he needs to do without ever “big leaguing” his team.  He’s another example of “not forgetting what got you there.”

One common thread in my relationships with these leaders is that they all became my friends.  I’m not sure if that’s central to leadership in a technical arena or something which I require of people to “lead” me in a professional environment (and make no mistake, I would not want to be my boss), but the best managers and leaders I’ve had are the ones who’ve allowed their staff members to get close to them.

High risk?  Maybe, I suppose, if you squint your eyes and tilt your head.  High reward?  In my experience, absolutely.

To keep the meme going, I’ll tag..

• Jimmy May

-wp

this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.