Archives

You are NOT a Junior DBA.

If you found your way to my blog, the chances are good that you consider yourself a Junior DBA. The chances are also good that you’re wrong. I can hear you saying right now, “Damn, you’re good, how do you know all that?” Well, I’m glad you asked.

Perceptions

I still remember my first SQL Server User Group Meeting and feeling like the dumbest guy in the room. And for a couple of years, I continued to think of myself as a Junior DBA. So, I spent a small fortune on my own education. Took classes, attended meetings and SQL Saturdays, and paid my own way to my first PASS summit. However, I still felt like my skill set didn’t measure up.

Meanwhile, local community members tried to convince me that I wasn’t Junior at all. Sean McCown (Blog), Jason Massie, Tim Mitchell (Blog/Twitter), and Tim Costello (Blog/Twitter) all encouraged me to go out and find my new job. However, perception is reality and if you think you are a Junior, then that’s what you are.

Revelations

About a year ago finally I started looking for new opportunities, and I realized something. I wasn’t a junior DBA and hadn’t been for some time. How did I come to this conclusion? The interview process. I had no problem passing any of the technical screens, nor did I have any major problems in any interview. I realized that my skills were better than the average bear. In fact, in one interview, the interviewer started jotting down notes as I pointed out several flaws in his SSIS Packages. Anyway as many of you know, last July I became a Senior Business Intelligence Consultant for a firm in Irving and since then my skills have increased exponentially as well. If you want a confidence boost, become a consultant and look at the majority of existing code out there. Trust me, it’s an eye opener.

Comparisons

I don’t know the origin, but I love the following quote.

Two campers in the woods see a bear approaching and one guy starts putting on his running shoes. The other guy asks, “what are you crazy? You can’t outrun a bear.” The guy with the running shoes replies, “I don’t have to outrun the bear, I just have to outrun you.”

The point is, when evaluating your skill level, compare yourself to the right group. I had been comparing myself to Brent Ozar (Blog/Twitter), Brian Knight (Blog/Twitter), Tim Mitchell (Blog/Twitter), Andy Leonard (Blog/Twitter), and Sean McCown (Blog). Notice a pattern there? They’re all SQL Server MVPs and a couple of them are Microsoft Certified Masters. So of course I felt like I was inferior. However, I don’t have to “outrun” these guys, I only have to run faster than the average DBA and compared to mere mortals, my skills are great.

Recommendations

This all came to a head recently when a friend from my user group lost his job and needed help finding a new one. I made some calls and such for him, but the most important contribution I had was advice about his attitude. Please allow me to pass it on to you as well.

If you’re still reading this, you’re most likely going to User Group Meetings, SQL Saturdays, and other events and therefore NOT a junior anything. Remember that what you think of your skills is what others think of them as well. Skills correlate to income and the more you think you’re worth, the better the offer you’re likely to receive. Take pride in what you’ve learned and be confident that you can learn any skill faster than the average person. Always try to secure a position a little higher than your current skill level and be prepared to raise your game so you can succeed in it.

If you’re not attending User Group Meetings and other PASS events like the upcoming SQLRally 2012, by all means get involved. SQLRally is in Dallas this year and I am in charge of the volunteers at this event so if you’d like to get more involved in the community, and I completely recommend it, message me. I’d be happy to help.

So if someone tells you that you’re a Junior DBA, or you’re telling yourself that very thing, be more confident in your abilities and always be working to improve.

Helpful Date Queries Which I Commonly Use

As I’ve mentioned before, I think I’m the most avid reader of my blog. I use it to document my work so that I don’t have to constantly re-invent the wheel. In this case, I find myself repeatedly searching for these scripts, often to populate SQL Reporting Services parameter defaults, so I thought I would share them with my readers as well.

-- Midnight of Current Day
SELECT dateadd (dd, 0, datediff (dd, 0, Getdate ()))
 
-- Midnight of last Friday (or whichever day).
/*The day of the week of date entered determines the result. Pick
a Friday, any Friday, and you will get last Friday.
Replace the - 0 with any increment of 7 to get previous weeks.*/
 
SELECT DATEADD (day,((DATEDIFF (day, '20120203', CURRENT_TIMESTAMP) / 7) * 7) - 0,'20120203')
 
--Day Number of Current year
-- Removed because the query was faulty. See comments. 
 
 
--First Day of Month
SELECT DATEADD (mm, DATEDIFF (mm, 0, GetDate ()), 0)
 
--Last Day of Month
 
SELECT DATEADD (s, -1, DATEADD (mm, DATEDIFF (m, 0, GETDATE ()) + 1, 0))
 
--First Day of Current Year
 
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()), 0)
 
--First Day of Previous Years
--Substitute '-1' with whichever number of years to reverse
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) - 1, 0)
 
--First Day of Next Year
SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) + 1, 0)
 
--Past 30 days.
SELECT DATEADD (dd, 0, DATEDIFF (dd, 30, GETDATE ()))
 
--Add in beginning and end of prior quarter.
SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()), 0)
 
--First Day of Prior Quarter
SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()) - 1, 0)

I’m not taking credit for these scripts as I’ve stolen borrowed these scripts from various places, most recently this excellent post.

Do you have any queries that you’d like to add?

Free Support Can Cost a Fortune

supportWhile performing some SQL programming work for a client they mentioned a problem they had with a vendor. I won’t name the vendor but I will say that it rhymes with Made2Damage. Anyway, the client spent most of the day trying to fix a problem with the help of telephone support. Hours were spent on the phone and at various attempted fixes. Now, I’m not blaming the vendor because the support representative can’t know this client’s setup as intimately as I do so they had to address every possible cause of the problem.

I asked him to briefly describe the problem. I then responded to him, “let me guess, the problem turned out to be the….” Of course, it was the same solution that he got through support, but it took five minutes to fix versus six or seven hours.

I asked why the client didn’t simply call me. He sighed and said that the vendor support was “free” (yearly paid support) while I would charge him per hour.

So, he wasted most of the day… but at least he saved fifty bucks.

User Defined Data Type Definition Script

Recently I worked on a project where every data type was user defined. In case you weren’t aware, SQL Server supports the use of User Defined Data Types. These are custom data types which are based on the standard types. For example, if your data always uses a two character string for states, you may want to create a data type called UDT_State.

However, this project had 40 UDTs and their names were fairly ambiguous. So, I created a “cheat sheet” script to refer to when working in that environment.

First, let’s create some user defined data types.

use tempdb
go
CREATE TYPE [dbo].[UDT_State] FROM char(2) NOT NULL;
GO
CREATE TYPE [dbo].[UDT_User_Id] FROM Int NOT NULL;
GO
CREATE TYPE [dbo].[UDT_Cost] FROM decimal(10, 3) NULL;
GO

The following script is compatible with SQL 2005 through 2008 R2. I believe it also works on SQL 2000 but don’t have access to a server to test it.

SELECT ST.[name] AS UDTName,
       CASE
          WHEN ST1.[name] LIKE '%char'
          THEN
             ST1.[name] + '(' + cast (ST.max_length AS VARCHAR (5)) + ')'
          WHEN ST1.[name] = 'numeric' OR ST1.[name] = 'decimal'
          THEN
               ST1.[name]
             + '(' + cast (ST.[precision] AS VARCHAR (5))
             + ',' + Cast (ST.scale AS VARCHAR (5)) + ')'
          ELSE
             ST1.[name]
       END AS BaseType,
       CASE ST.is_nullable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable
  FROM    sys.types ST
       JOIN
          sys.types ST1
       ON ST1.user_type_id = ST.system_type_id
 WHERE ST.is_user_defined = 1

Special thanks to Jeff Rush (Blog/Twitter) for the idea to write this script.

Interesting Integration Services (SSIS) Error and Solution.

While troubleshooting an existing 2008 R2 package the other day I received the following error.

Error HRESULT E_FAIL has been returned from a call to a COM component.

SSIS Breakpoint Error

The error occurred when I was using breakpoints and watch windows to diagnose problems in the package. The breakpoint behavior was off because even when there wasn’t a breakpoint on this particular task, it halted anyway. When I clicked on the Break OnPreExecute event, the error popped up. I took the following steps to attempt to fix the problem because the package was too involved to start over.

  1. Restarted SQL Server Business Intelligence Development Studio (BIDS) and the computer.
  2. Tried editing the package on a different machine.
  3. Updated SQL Server to the latest cumulative update.
  4. Attempted a repair install.

None of that worked. Surprisingly, what did work was simply copying and pasting the task into the same package. The task copy did not have the same problem as the original. I deleted the original and everything ran as expected.

Hope this post helps someone who runs into a similar problem.

How to Pass GetDate() as a Stored Procedure Parameter

The Problem

When you pass GetDate() as a parameter to a stored procedure, the following error is returned:

EXEC dbo.ProcDate GetDate()

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’

When I ran into this problem, I found this helpful article which suggests that I should assign the value of GetDate() to variable and then pass the variable as the parameter. While that does work, I wasn’t in a position where I could use a variable so I needed to find another solution.

The Solution

My solution was fairly simple. Define a default value for the parameter as GetDate() like so.

IF EXISTS
      (SELECT name
         FROM sysobjects
        WHERE name = N'ProcDate' AND type = 'P')
 DROP PROCEDURE ProcDate
GO
 
CREATE PROCEDURE ProcDate
   @Date DATETIME = NULL
AS
   SET @Date = coalesce (@Date, Getdate ())
 
   SELECT @Date
GO

Now the stored procedure works correctly. If a date is passed as a parameter, the stored procedure uses it, otherwise it defaults to GetDate().

Exec.ProcDate '11/23/2011' -- returns 2011-11-23 00:00:00.000
 
Exec.ProcDate              -- returns 2011-11-12 15:57:27.623

Incidentally, I realize that this stored procedure isn’t “production ready” because it will fail if someone passes “Bleh” in as the parameter. I deliberately left out the error checking to give avoid clutter.

The Made2Manage Favorites Bar Must Die

As I’ve mentioned before, the Made2Manage Favorites Bar is a buggy mess. I regularly get questions from my clients and readers of this blog asking why M2M screens return incorrect data or otherwise misbehave. One of my first suggestions to them is to disable the favorites bar. However, disabling the bar isn’t intuitive so I thought I would quickly post how to do it.

First, it is not enough to simply un-check the toolbar. If you un-check the toolbar, but leave the M2M screens attached in it, you haven’t really disabled it.

So, select the toolbar and then left click and drag the items off it one at a time. Where you drag and release them doesn’t matter. It feels awkward, but you know when you’ve “grabbed” the item correctly when it forms an unusual black cross. Unfortunately, I couldn’t catch that in a screenshot.

After you have cleared all of the individual items from the toolbar, then you can disable it by selecting Windows, Toolbars, and un-checking the Favorites Bar.

Incidentally, there isn’t a way to keep users from loading the toolbar up with screens again afterward, and I find that they often do. These incorrect record number issues tend to be cyclical because the same users have the problem repeatedly after they enable the toolbar again.

Hey, at least it’s job security for M2M Adminstrators. 🙂

This One Time... At PASS Camp...

Like many of you, I have been finalizing my PASS Summit plans, getting ready for the trip to Seattle.

For the past week, my girlfriend has been asking weird questions like, “Have you packed your sun screen?” This makes no sense because there’s no way I’ll get a sunburn in Seattle in October. When I ask her about it she just giggles and moves on.

She’s also reminded me to bring other things such as a bathing suit, hot dogs, and marshmallows. When she mentioned the marshmallows, I finally got it. She’s teasing me about PASS as if I’m going to summer camp. And if you think about it, summer camp is a pretty apt metaphor.

As I type this I am really excited about the trip. I’m looking forward to seeing all of my friends, many of whom I haven’t seen since last year, and doing all sorts of fun “activities” with them. We may not be going hiking, fishing, or swimming, but we will be networking, singing at SQL Karaoke, and attending the vendor parties.

So, here’s to all of my PASS Camp buddies. I can’t wait to see you again this year.

Oh and at camp this year, I’ll also be attending the PUSS Party. God knows what’ll happen. I just hope nobody brings a flute.

It's a PASS Thing, You Wouldn't Understand...

Why would you do that for free?” she asked. I was kind of surprised and paused for a minute because it had been some time since I had thought about it.

To set the stage, I was visiting a relatively new client and since they have casual Fridays, I wore my speaker shirt from SQL Saturday Chicago which is personalized with my name. The client asked about it and I told her that I often volunteer and present at SQL Saturdays and User Group Meetings. The conversation went like this:

Her: How much does that pay?
Me: Umm… nothing. It’s volunteer.
Her: Well, they do reimburse you for travel expenses, right?
Me: No, that’s my responsibility.
Her: I don’t get it. Why would you do that for free? Why work for nothing?
….. Pause
Me: It’s not work because I love doing it. I enjoy teaching and presenting, and well… just hanging out with these people.
….. Skeptical look from the client.
Her: It’s a career thing, right? There are people who will advance your career there?
Me: Umm.. there are some career aspects about it, but mostly I went to that event, and really all of these events, because I like the people. Don’t you travel to spend time with people you like?
Her: Well yeah, but not other [Business Type Removed] people. It’s not a social thing.
Me: Well, I guess it’s a PASS thing, a geek thing.
Her: I don’t get it.
Me: I’m going to several events in the next month and maybe your DBA’s would like to attend Austin’s SQL Saturday…….

Terrible Photoshop Skills, I Know

It’s a PASS thing, you wouldn’t understand.

I’ve been a member of this community for a few years and it’s a big part of my life. If you haven’t gotten involved and embraced the SQL Community yet, then what are you waiting for?

Why attend these events? What do you get?

  1. Free or inexpensive training.
  2. Access to some of the best minds in SQL Server.
  3. Exposure to new SQL Server ideas and features.
  4. Inspiration to increase your skill set and improve your career.

It goes without saying that those benefits are important. However, they’re not the most important aspect of these PASS Events. For example, the knowledge is great, but I can learn from books, recordings, blogs, etc. Thanks to the community, tons of free information can be found on the Web.

So, what’s the most important aspect of these events? For me, it’s all about relationships. Building bonds with these people is everything and all of the other stuff is a by-product of that. The events are fun, especially if you volunteer and speak at them. I love this community. That’s the intangible aspect that I couldn’t relate well to the new client.

Upcoming Events

This Saturday (10/1), the fine folks at CACTUSS will be hosting SQL Saturday #97. Some of the best minds in SQL Server will be there to share their knowledge and passion for SQL Server. Oh, and I’ll be there as well to present the following two topics:

Data Warehouse Mistakes You Can’t Afford to Make
Many data professionals understand the basics of Data Warehouse design, including Dimension and Fact Tables, slowly changing Dimensions, and the use of meaningless surrogate keys. However, it isn’t until you’ve created a dimensional model and put it into production, that you realize just how much of an impact seemingly trivial mistakes can make. They can hobble performance, allow inaccuracy, and perhaps worst of all, inhibit adoption and usage of the new system.

Learn how to avoid many common mistakes, from someone who’s made them and then found ways to correct them.

SSIS Done Right With Package Configurations
Everyone starts creating SSIS Packages the same way. We hard code connection strings, file locations, etc. Package Configurations are a powerful way to control your packages at run time. This allows you to seamlessly move packages between servers, dynamically assign data sources and destinations, cycle through data sources, and create reusable code.

In this presentation, I’ll demonstrate an easy to understand three step process which you can use with all of your packages to increase your productivity in SSIS.

PASS Summit 2011

The Pass Summit is happening the week of October 10th. The PASS Summit is a wonderful experience, that you just can’t get anywhere else.

This year I’ve been selected to give a five minute lightning talk. DBA Lessons Learned from The Godfather. In that presentation, I’ll share how themes from the Godfather novel and movies apply to our profession and the SQL Community.

I’m really looking forward to these events. I can’t wait to see some of my friends again and get re-energized about SQL Server, blogging, and the community.

I hope to see you there.

How to Ask for Help

Like many other technical bloggers, I receive requests for help on a regular basis. Since I’m constantly learning and evolving I approach others for help as well. A couple of years ago, I received an email from a new reader which began with:

Always two there are, no more, no less: a master and an apprentice.

The line is from Star Wars, of course. Being a huge geek, it’s easy to understand how his email got my attention right away. His approach was polite, respectful, and most importantly demonstrated that he had in fact read some of my blog. That got me thinking, what are some tips I could give my readers on how to ask for help? Of course, I couldn’t just produce a list. In my geekitude, I have prepared a list of movie scenes to explain my suggestions.

Show Respect

What have I ever done to make you treat me so disrespectfully? – Godfather

Be respectful in your request and if at all possible, offer your friendship before you need help. Attend user group meetings and get to know people. Volunteer. If it’s a forum, do your best to participate and help others in the forum. As in the movie, you’re more likely to get help when you need it if you already belong to a group.

Be Nice (Politeness)

Be Nice

If somebody gets in your face and calls you a &^*%*$!!!, I want you to be nice. Ask him to walk. Be nice. If he won’t walk, walk him. But be nice. If you can’t walk him, one of the others will help you, and you’ll both be nice. I want you to remember that it’s a job. It’s nothing personal.- Dalton in Road House

Don’t go overboard but a complimentary remark never hurts when asking for help. If you’ve seen them present, read their blog, or answer questions in forums, complement them on their abilities. Just make sure that your compliments are sincere. I can’t tell you how many “requests” I’ve gotten for help which sounded more like demands for help. People don’t tend to respond well to demands.

Communicate Clearly

Chris Tucker

Do you understand the words that are coming out of my mouth?!?!? – Detective Carter in Rush Hour

Take some time and compose your question so it can be easily read and understood. If I have to waste cycles trying to decode your question, or need additional information, chances are that I won’t bother. In my experience, most people are like this. Depending on your problem, explain the steps you have taken to remedy your issue and the result of those attempts. Take time to use a code prettify-er before posting it and if possible the code to generate a sample data set people can use to help you. Make it easy for people to assist you. Now that I’ve just told you how complete your questions should be, remember that at the same time you should….

Be Brief

Let me ‘splain… No, there is too much. Let me sum up. Buttercup is marry’ Humperdinck in a little less than half an hour. So all we have to do is get in, break up the wedding, steal the princess, make our escape… after I kill Count Rugen – Inigo Montoya in The Princess Bride

The longer your question, the less likely people are to read it. Therefore, it’s in your best interest to be as brief as possible while still providing the background information to solve your problem. Try to leave rants or opinion out of it and just state the facts.

Work for Your Own Answers

Wax On Wax Off


Before asking for help, do your due diligence. RTFM, Google it, and try to find the answer yourself. Nothing exasperates people more than being asked questions with simple answers that can be found in two minutes with a search engine. Make sure you indicate that you have already searched for the answer so others will take time to answer you. Sometimes the answer to a problem is as simple as knowing the right search terms.

Ask for Guidance, Not Solutions

It is like a finger pointing away to the moon. Don’t concentrate on the finger or you will miss all that heavenly glory. Bruce Lee

This goes along with RTFM. People are more likely to help you if you ask for general direction and not the complete solution to your problem. People do not want to do your homework, but they will direct you to information sources that will help you. A question framed like, “Can anyone recommend a resource where I can learn to…..” is likely to be answered.

Be Patient

We’re running out of time!!!! – Jack Bauer in 24

You often see this in forums. “Help!!!! My Server is down and I have no backups!!!!” Then five minutes later the person bumps their topic again because people haven’t responded fast enough. If you have a time sensitive emergency, then pay for immediate support. Don’t expect a forum to substitute for that kind of assistance. Your behavior won’t get your question answered any faster, and may in fact alienate people who would normally be willing to help you.

Don’t be Greedy

The point is ladies and gentlemen that greed, for lack of a better word, is good.- Gordon Gekko in Wall Street

Yes, I know that the movie quote says the opposite, but stay with me. Similar to my last point, if you continuously ask questions without reciprocating, you may find that the answers stop coming. People don’t want to feel used, so you should prioritize your issues/questions and use community resources sparingly.

Don’t Argue

Pai Mei

That, my dearest, depends entirely on you. Now, remember: no sarcasm, no backtalk. At least not for the first year or so. You’re gonna have to let him warm up to you. – Bill in Kill Bill, Volume 2 (speaking about Pai Mei, plucker of eyes.)

I’ve seen this more times than I can count and it’s often hilarious. Some newbie asks for help on a forum regarding how often he should be shrinking his database. Paul Randal (Blog/Twitter), who has forgotten more about SQL Server than most will ever learn, advises him that shrinking should be avoided. The newbie then proceeds to argue because he heard somewhere that database shrinking is akin to nirvana. Will Paul pluck your eye out like the infamous Pai Mei? Probably not, though I wouldn’t bet against him having those skills. However, if you argue with folks trying to help you, you’ll most likely get less assistance next time.

If Possible, Offer to Pay

Give Me Paw!!!

Listen! We’re not just doing this for the money! We’re doing this for a S*** LOAD of money! -Lone Starr in Spaceballs

I’m not suggesting that everyone is motivated to help others by money, however it doesn’t hurt. In fact, you’ll most often find that people will refuse money that is coming out of our personal pocket. However, it does indicate to them that you are serious about getting help and answers. Also, offering to buy someone lunch or a drink or whatever goes a long way. I’ve bought more than a few meals in my day as a way of thanking people who helped me.

Follow Up

“Just pay it forward.” – Thorsen in Pay It Forward

If you ask a question on a forum, and you find the answer yourself, make sure to go back and update the thread with the answer you found. This gesture doesn’t benefit you personally, but it will help others when they have a similar problem.

Also, if a answer or suggestion helped you, follow up with the person and let them know. This seems obvious, but you’d be surprised how often people neglect to do this. Let’s face it, it feels good to receive a thank you message, and you’re more likely to get help with your next question.

So, that’s my list. Do you folks have any other suggestions?

Page 7 of 33« First...56789...2030...Last »