I went to SQL Saturday in Tampa!

 My husband and I got married May 3 (I’m not telling the year).  As we were rushing to the airport en route to our honeymoon, we realized that in the chaos of the wedding, we had almost forgotten a present for our mothers on the upcoming Mother’s Day.  I somehow found the perfect card given the circumstances, and we mailed them just as we were leaving.  Here is a rendition of the card we each sent our mothers that year:

 

The reason I’m thinking of that anecdote is that here it is on Tuesday, and it’s my turn for a blog post, and I’m in sort of a rush again.  Not on my way to Europe this time, but anyway; I’m busy.

I wanted to do a write up of SQL Saturday #62 in Tampa which occurred two weeks ago, but I feel like the content is going to be as dense and informative as the above referenced greeting card. 

Suffice it to say that I had a fantastic time.  Organizers Pam Shaw and Jose Chinchilla are to be commended for the hospitality, the food, the speed pass idea.  It was a great weekend. 

It was warm in Florida.  This cannot be overemphasized.  I really enjoyed the Sun (Blog | Twitter) and the way its shininess let me cavort around mittenless.  I hope to return to Tampa every time there is a SQLSaturday in the winter.

My presentation was well attended and well received.  Lots of folks came, including many people who work with SSIS and are pretty famous for it.  Fortunately for me their presences neither made me faint nor throw up, always a bonus. 

I left with more friends and professional connections, and perhaps most importantly, the most awesome SQL Saturday presenter’s shirt EVAH:

If only I had something yellow to wear. Not a polo.

———————————————————-

Finally, I’d like to “roll credits” so to speak and give a shout out to all the great folks I got to spend time with that weekend.  (Loving the networking tab on the SQL Saturday Page!).  Can’t wait to see you all again.

Name/Twitterhandle:

Aaron Nelson @sqlvariant   
Adam Jorgensen   @adam_jorgensen
Andy Warren    @sqlandy
Bobby Dimmick    @kr4ster
Brad Schacht   @bradleyschacht
Brian Knight @BrianKnight
Brian McDonald    @briankmcdonald
David Taylor    @dyfhid
Denny Cherry    @mrdenny
Jorge Segarra    @sqlchicken
Jose Chinchilla    @sqljoe
John Welch    @john_welch
Jason E Bacani    @jebacanisqldude
Jack Corbett    @unclebiguns
Geoff Hiten   @sqlcraftsman
Gareth Swanepoel    @garethswan
Ed Wilson   @scriptingguys
Erika Bakse    @baksedoesbi
Eric Wisdahl    @old_man_fish     @EricWisdahl
Kevin Boles    @thesqlguru

Mike Wells @SarasotaSQL
Pam Shaw    @pamshaw
Troy Gallant    @gratefuldba
Tim Radney    @tradney
Stacia Misner    @staciamisner 

——————————

And lastly, SQLRally voting for the BI Track will be next week.  Don’t forget to vote! (for me! ) 

Noir SQL… Or, a Hardboiled Approach to Getting the Job Done

You can tell a lot about my state of mind by the books I’m reading. Lately, it’s Urban Fantasy with a Noir feel to it. Specifically, I’m reading Mike Carey’s Felix Castor series, and I just finished a book by Richard Kadrey called Sandman Slim: A Novel. I love the anti-hero. The protagonist who is gritty and dirty and has a few great scars is my kind of guy. He unapologetically breaks the rules and isn’t all, “it’s more about the journey than the destination.” For him, destination is what matters, no matter now you got there.

Lately, I feel a bit like the scarred anti-hero. I’m doing some things in a production environment that I’m not totally thrilled about, and I wish I could stop the line and do things the “right” way. I want to use SSIS to transform data. I want to encapsulate processes into neat, repeatable, parameterized modules. But, you know what? When there’s a same-day turnaround on a request, you make do. You go a little Noir on your T-SQL, know what I mean?

I want to show you two things that I’ve actually done in the past few weeks. No, given a nice, neat environment, this SQL might never have been written. Am I proud of it? Well, yes. Yes I am. At the end of the day, I got the customer what he needed. Was it pretty? No. I’m cool with that. Being the anti-hero is kind of fun every once in a while.

Fixed-Width Output

I needed to give a guy a text file in fixed-width format. I had a process from my predecessor that just wasn’t working. The file was already late. So here’s what I did. I’m using the AdventureWorks database to show an example.

SELECT
	LEFT((ISNULL(Title,'')+SPACE(50)), 8)+
	LEFT((ISNULL(FirstName,'')+SPACE(100)), 20)+
	LEFT((ISNULL(LastName,'')+SPACE(100)), 30)+
	LEFT((ISNULL(MiddleName,'')+SPACE(100)), 5)+
	LEFT((ISNULL(EmailAddress,'')+SPACE(100)), 35)+
	LEFT((ISNULL(Phone,'')+SPACE(100)), 25)
FROM AdventureWorks.Person.Contact	;

The result:

Paste it into Notepad and see how it looks:

I save the text file and send it on. Pour myself a whiskey, neat, and light up an unfiltered Lucky Strike.  Okay, not really, but you know what I mean. 

A quick run-down:

ISNULL: If any of the values I’m concatenating are NULL, then the entire string will come back as NULL. I wrap all of my columns in ISNULL like so:

ISNULL(Title, ‘’)

This sets the value to an empty string if the value is NULL.

SPACE: This handy little string function will pad the given number of spaces onto the result you return. I want to make sure I end up with enough padded spaces to fill out the fixed-width portion of that column. So, I pad the output:

ISNULL(Title, ‘’)+SPACE(50)

This will give me the output from the Title column, plus 50 spaces.

LEFT: Now, not every value coming out of the database is going to have the exact same number of columns. So, I use the LEFT function to trim it down to the exact length I want. LEFT will take the left-most number of characters you tell it to. If I say,

LEFT((ISNULL(Title,”)+SPACE(50)), 8 )

I’m telling it to give me characters 1-8 that are returned. Since I’ve padded my output with spaces, it’ll be the result from the column, plus as many spaces as I need to pad the output to 8.

Pretty? No. Functional? Yes. Noir SQL? Absolutely.

Remove Unwanted Characters

Next up, I have a source file I use from another department. It comes in Excel format, and includes a phone number. I’m supposed to get something that looks like this: 1112223333. Nice, neat, simple. What do I get? A hodge-podge of phone number formats. I’m looking at something like this:

CREATE TABLE PhoneNumber
(
	PhoneNumber varchar(50)
); 

INSERT INTO PhoneNumber(PhoneNumber)
VALUES
	('1112223333'), ('(111) 222-3333'), ('111-222-3333'), ('111 222 3333'); 	

SELECT PhoneNumber
FROM PhoneNumber

Okay. So I need to clean these numbers up quickly. Destination, not journey, my friends. I’m the anti-hero. I import the data into SQL Server using the Import/Export utility so I can manipulate the data. Then, I run this nifty little REPLACE statement:

SELECT PhoneNumber,
	CASE
	WHEN ISNUMERIC(PhoneNumber) = 0
		THEN REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(PhoneNumber, '-', ''),			--Strip out dashes
				' ', ''),							--Strip out spaces
			')', ''),								--Strip out close parenthesis
		'(', '')									--Strip out open parenthesis
		ELSE PhoneNumber
	END as FormattedPhoneNumber
FROM dbo.PhoneNumber

Check out the results:

Sweet. It’s quick, it’s dirty, and it saved me having to wait on the source data provider to clean things up on his end. I turn the query into an UPDATE statement, and I’ve got clean data to import.  Again, a run-down of the functions:

ISNUMERIC: Tells me whether the value I’m passing is qualifies as a number or not. NOTE: It recognizes hexadecimal as a number, so use carefully. I set up a CASE statement that asks if the value is numeric. If it is, that means I don’t have any characters like “(“, “)”, or “-“ in there. If not, I apply a nested REPLACE to the value.

REPLACE: Replace is awesome. I can say something like this: REPLACE(PhoneNumber, ‘-‘, ‘’). This is saying that if I find a dash, I want to replace it with an empty string. What’s really cool is that I can nest them. So, I can tell it to remove the dashes, then the spaces, then the open parenthesis, and finally the close parenthesis in one statement.

Bottom line: Sometimes things just have to get done. The difference between an anti-hero and a true antagonist is that we anti-heroes know to go back and do things the right way as soon as we get a moment to breathe. In the meantime, don’t apologize for leaving behind a few unmarked graves when you need to get the job done. We’re anti-heroes. We have the scars to prove it.

T-SQL Tuesday #14—Julie’s 2011 Goals in “the Cloud”

 It’s T-SQL Tuesday and the topic is Technical Resolutions for 2011.  Thanks to MidnightDBA  (Blog|Twitter) for hosting this month’s event. 

 My 2011 Technical Resolutions can best be summarized with a song….

 I’m just kidding—I can’t summarize my goals with a song.  I don’t write songs. But I am great at surfing the internet and lookie what I found at www.wordle.net. It was a handy dandy, feel free to distribute your  creations, category cloud maker!  So here is my 2011 Technical Resolution Cloud:

T-SQL Tuesday #14 – Audrey’s (Career) Aspirations for 2011

Here we are again for another T-SQL Tuesday. This month’s event is being hosted by MidnightDBA (Blog|Twitter). The whole crazy concept is the brainchild of Adam Machanic (Blog|Twitter). If you’re interested in what this thing is about, check out this month’s invite.  A quick bit of gushing praise for the whole T-SQL Tuesday thing: For me, deciding what to write about is the hardest part of blogging. When someone tells me, “Hey Audrey. Write about this. And we promise a ton of people will come read about it”, well, you don’t have to tell me twice. I’m on it like the paparazzi at a celebutante convention.

The topic of this month’s event is “Resolutions”, which is, you know, totally apropos since it’s the first month of the year and all. Personally, I don’t make many resolutions, and they’re usually boring. For example, last year’s personal resolutions included: 1) Lose 10 pounds (I didn’t) 2) Drink more water (I did), and 3) Clear out clutter (Sort of, a little). I do establish career goals, and as my mama always said, “They aren’t real until you write them down”. She was a list-maker. She would make a list, and if she did something that wasn’t on the list, she’d add it just so she could cross it off. Watching her made me a list-maker too. It’s part of my morning routine to sit down and write down what I’m trying to accomplish that day. Yes, I said write. Like with pen and paper. I need the ritual. So it makes sense to get my to-do list together for 2011. Thanks to MidnightDBA for giving me a good excuse to really think about it.

Audrey’s (Career) Aspirations for 2011

1) Learn more about SQL Server internals. I’ll be reading Microsoft SQL Server 2008 Internals by Kalen Delaney (Blog|Twitter), Paul S. Randal (Blog|Twitter), Kimberly L. Tripp (Blog|Twitter), Conor Cunningham (Blog), and Adam Machanic (info in 1st paragraph). This book has been sitting on my shelf, glaring at me for not reading the entire thing for far too long. It’s time to go cover-to-cover, baby.

2) Learn more about Analytics, using SQL Server as well as other products. I want to get better at the UDM/cube/presentation portion of the BI Stack. I’m still figuring out the right approach for this aspiration. But, I can assure you that Project Crescent and BISM are somewhere on the plan.

TANGENT: When I hear Project Crescent, I immediately think “crescent roll”, and then I think “Crescent City” which is New Orleans, and then I think of beignets, because they are tiny bits of powdered sugar-dusted heaven. Then, I get a little homesick, because there aren’t many places around Atlanta where you can get a good beignet. I grew up near Houston, which is close enough to NOLA that the good food tended to bleed over into our part of Texas. They should have just named it Project Beignet to save me the time it takes to get through my stream of consciousness. :END TANGENT

3) Re-read The Data Warehouse Toolkit by Ralph Kimball and Margy Ross. Why? Well, I haven’t read it in a couple of years, and I have a build-out of a dimensional model on my plate. Reading this book again is like stretching before the big game. I’ll feel warmed-up and ready to go when the project really gets rolling.

4) Learn PowerShell (for SQL Server). You know, there are a lot of reasons for learning PowerShell, but the one that motivates me the most is this: My first programming language was Turbo Pascal 7.0 during my senior year of high school in 1994. (I’ll save you the math… I just turned 35. [sigh…]) My first RDBMS was Oracle 7.3 in 1996. Neither had snazzy GUI’s to help me limp along as I was learning. I love how my world has come full-circle. Here we are in 2011, and people are singing the praises of a non-GUI-based way to interact with SQL Server. I want in on the fun. AND… I’m tired of hearing from Aaron Nelson (Blog|Twitter) about how great it is and not knowing for myself.

5) Finish my certifications. I got my MCTS certs for SQL Server 2008 Business Intelligence and SQL Server 2008 Database Development late last year. I promised myself that I’d go on to the MCITP exams this year. This one has a timeline too… I’ll get both before the end of the summer. I’m toying with the idea of going after SQL Server 2008 Database Administration too. You know, because they’re there. And I’m so ridiculously competitive that the idea of leaving the tests untaken is bothering me.

6) Blog, and blog consistently. My fellow Datachix, Julie Smith (Blog|Twitter) and I promised each other that we’d each blog every other week. That’s 26 blog posts for me this year. I’ll go a step further and say that 20 will be technical posts. You’re not getting much fluff from me this year, my friends.

7) Speak. I will present more. I will learn to present virtually. I will rock the house with my awesome, well-prepared, techically relevant, and entertaining presentations.

8 ) Finally, I will be a great consultant. I already know how I’m spending 2011 from a client standpoint, and I’ll be working to make sure that they look back on 2011 and remember it as the year that they finally got their data straight, their processes together, and their analytics moving in the right direction.

So, in conclusion, my 2011 aspirations are: Read, Study, Read, Learn, Test, Blog, Speak, Rock. Easy-peasy, right? Whoo-boy! I’ve got to go. I need to get started.

Wait, before I go… My 2011 aspiration for all of you is that you have a wonderful, satisfying, and all-around kick-ass year. Make time for the things you love. Learn something new. Try something you’re scared of. Make some new friends. Go for a walk in the rain without an umbrella. Watch a sunset or two… and maybe a sunrise. Tell the people you care about how awesome they are. When presented with a great opportunity, go for it. When 2011-12-31 23:59:59.999 rolls around, you’ll be glad you did. I’m totally rooting for you!

Thanks for reading all the way through my list, and if you see me, ask me how I’m doing on it. There’s nothing quite like public accountability to keep me honest.

Rock on, my friends…

—Audrey

Julie to Present “Cool Tricks to Pull from your SSIS Hat” at SQL Saturday #62 in Tampa FL

I am honored to be presenting at SQL Saturday #62 which will be held in (hopefully) warm Tampa, Florida on January 15, 2011. The title of my presentation is Cool Tricks to Pull from your SSIS Hat, and it covers the basics of SSIS variables and the Expression language. I will also be participating in the Women in Technology panel discussion.

This event, the first of 2011, will be bustling with fantastic speakers. I’m especially looking forward to the Powershell/SSIS Smackdown with Aaron Nelson (Blog|Twitter) and Mike Davis (Blog|Twitter).

I am also going to the PreCon which will be the Friday before the main event. This is a huge bargain and I didn’t want to miss the opportunity for an entire day of training on (SAN) storage and virtualization for DBA’s and BI for $99. I imagine I’ll be switching from room to room throughout the day. Sign up before tomorrow to join me at this price!

From Pam Shaw and Jose Chinchilla on the PreCon–>

We will also be hosting a Day of Data on 1/14/2011, the day before SQL Saturday #62 in Tampa at the Italian Club in the historic Ybor City district. We are offering 2 all day sessions from which to choose. For the DBAs we have Denny Cherry presenting Storage and Virtualization for the DBA. For the BI focus We have Stacia Misner presenting Business Intelligence End-to-End. The cost is only $99 per person thru 1/5/2011, after that the price goes to $109. This price includes coffee, juice and donuts, lunch, and course materials. Click here to register for Day of Data.