Tuesday, April 28, 2009

Speaking at Tampa Bay SQL User Group

I will be speaking at the Tampa SQL User Group on Tuesday, May 19th.  I’ll be speaking on SQL Server Profiler which I have presented at OPASS and SQLSaturday #8 – Orlando. 

I look forward to meeting everyone who is part of the group and re-connecting with those I have met at other events like SQLSaturday.  Please don’t be offended if we met and I can’t remember your name, it is a MY failing, not yours.

Tuesday, April 21, 2009

JumpstartTV Featured Content

My video, Using fn_trace_gettable to Import Trace Results, is the featured content on JumpstartTV today.  Check it out.  JumpstartTV is a site that features short (< 5 min) how to videos focused on SQL Server.

Thursday, April 16, 2009

Accepted to Speak at SQLSaturday Pensacola

I found out this afternoon that my session, Getting Started with SQL Server Profiler, was accepted and put on the SQLSaturday Pensacola schedule.  This will be the third time overall I’ll have done this presentation and second SQLSaturday. 

It’s always an honor to be chosen to speak especially when you look at the other speakers.  This is almost like a SQL Server MVP meeting with Steve Jones, Andy Warren, Rodney Landrum, Brad McGehee, Jessica Moss, Brian Knight, and others that either are or could be MVPs.  As you can see just from that short listing it is quite a line up.

I actually didn’t expect my session to be accepted with Brad McGehee having submitted a Profiler session as well, and considering that he wrote a book on it.  Fortunately his session is a more advanced session, that I plan on being in, so I can improve my understanding.

I’m looking forward to seeing Steve, Brian, and Rodney again and then having the opportunity to meet in person several people I follow on on-line like Tim Mitchell, Jessica Moss, and Brad. 

It’ll be a great time to learn and network (I’ll be working on the ideas that Andy has in his blog series).

I’m also interested in meeting YOU! Here’s the link to register.

SQL Quiz #4 – Great Leaders in My Career

Chris Shaw has posted another SQL Quiz and I was tagged by Grant Fritchey

What is a Leader?

My definition of a leader is someone who inspires others to reach beyond their comfort zone. 

The inspiration can be through example, encouragement, or challenging.

An Early Example

I’m going to start by going back to one of my first jobs, which was at McDonald’s.  Anyone has been at McDonald’s and seen that there are many managers that work at the restaurant, and, to be honest, most of the time they are not great leaders, but where I worked there was one manager who was a good leader, Dennis Bouchard.  He never asked a crew member to do something he would not do, and was never afraid to be challenged by one of us high school kids.  He and I would go nose to nose on some issues and that is what I respected him for.  He knew what needed to be done, challenged us to get it done, waded in when we were struggling, and allowed us to disagree with how it should be done.

A Current Example

The person who is currently challenging me to do more is someone else who was tagged by Grant, Andy Warren.  I met Andy in the fall of 2007 at a one day seminar by Joe Celko before the first SQLSaturday in Orlando.  I got to know him through OPASS and having him come in and do some consulting on a project at work.  Since meeting Andy I have begun blogging, written 4 articles for SQLServerCentral, done several videos for JumpstartTV, spoken at OPASS, spoken at SQLSaturday #8, committed to speak at the Space Coast User Group, and submitted 2 sessions for the PASS Summit.  In doing all these things my knowledge and experience in SQL Server had probably doubled.  If you had told me 2 years ago that I would have done all these things and met (online and in person) the people I have met, like Grant, Gail Shaw, Steve Jones, and others, I would have told you that you were nuts!  All this because Andy took an interest in me and challenged and encouraged me to do more.  Thanks Andy.

I have not seen either of these folks tagged yet, so I’ll tag:

K. Brian Kelley
Lynn Pettis – a new blogger, but an experienced SQL Server guy.

Wednesday, April 15, 2009

Introduction to Profiler published on SQLServerCentral

My article Introduction to Profiler has been published on SQLServerCentral today.  This is part of one of a series that I am working on and covers the basics of Profiler.  The rest of the series will build to more complex uses of Profiler.

Tuesday, April 14, 2009

The Deception of IsNumeric()

Has anyone else ever used the IsNumeric() function to try to eliminate this error (or similar):

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

yet still get the error? Isn’t that what the function is for?  Here’s what is says in BOL:

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0

But run this code (generates a list of ASCII Characters):

;WITH cteNumbers AS
   
(
   
SELECT TOP 255
        ROW_NUMBER
() OVER(ORDER BY NAME) AS n
   
FROM
       
sys.all_columns AS AC
   
)
   
SELECT
       
n AS ASCII_code,
       
CHAR(n) AS [character],
       
ISNUMERIC(CHAR(n)) AS is_numeric
   
FROM
       
cteNumbers
   
WHERE
       
n <=255 AND
       
ISNUMERIC(CHAR(n)) = 1

And you’ll see that the first 10 rows and last 8 rows returned are not what I would consider numeric.  Or how about this:

SELECT
   
ISNUMERIC('20,00')
GO
SELECT
   
CONVERT(DECIMAL(9,2), '20,00')

The first statement returns 1, while the second fails. 

Here is the last interesting behavior of IsNumeric() in relation to Cast/Convert provided by Frank Kalis on this SQLServerCentral thread:

SELECT ISNUMERIC(0X0e) AS E, ISNUMERIC(0X0fAS F

E           F          
----------- -----------
0           0

(1 row(s) affected)

WHILE

SELECT
CAST(0X0e AS INT) AS E, CAST(0X0f AS INT) AS F

E           F          
----------- -----------
14          15

The moral of the story is that IsNumeric() <> CanBeConvertedToNumeric().

So what is the answer?  I don’t know.  You would need to customize the solution to meet your specific situation.  Jeff Moden suggests an IsAllDigits function in this thread on SQLServerCentral for instances where you want to eliminate rows with non-numeric characters.

Do you have anything that you recommend?

Friday, April 10, 2009

PASS Sessions Submitted, Should I Be Committed?

Well, even though the deadline was extended 2 weeks, I got 2 sessions submitted for the PASS Summit submitted by the original deadline.  Now the question is, should I be committed for even attempting to speak at a national (international) event?

Here are the sessions:

Profiler: An Underused Tool

Profiler has been around for awhile, but is often left in the DBA's toolbox. Profiler is a flexible tool that can be used for Auditing, Monitoring, and Performance Tuning

Goals:

  1. Understand when, why, and how to use Profiler
  2. Understand the differences between Profiler and server-side tracing.
  3. Understand performance and security considerations for using Profiler.

Dive into the Default Trace

Beginning with SQL Server 2005 there is a server-side trace installed and started by default in every SQL Server installation. Not many DBA's take advantage of the wealth of information collected through this trace. This trace includes 34 events in 2005 and 35 events in 2008 that audit non-DDL activity on your SQL Server.

Goals:

  1. Learn what events are included in the Default Trace.
  2. Learn how to query the Default Trace
  3. Learn how to process the Default Trace files to keep the information beyond the default amount of data (5 20MB files).

Now that I’ve submitted, how about you?

Thursday, April 9, 2009

Deploying Custom SSIS Components

This post is really for me.  I have written 1 custom component for SSIS 2005 and wrote an article on it for SQLServerCentral that was recently re-published.  In the discussion someone asked for specific steps for installing and using it.  Well, since it has been about a year, I couldn’t remember all the specifics so I’m blogging the steps so I have place to go the next time I need it.  So on with the steps:

  1. Register the component in the GAC (Global Assembly Cache).  There are 2 ways to this:
    • Drag and drop the component into C:\Windows\Assembly
    • Use the GAC utility that that is part of the Visual Studio SDK and the Windows SDK.  Here is the syntax:
        gacutil.exe  /if Path\AssemblyName
    • Depending on which one you installed it will be located in one (or both) of these locations if you used the default install:
      • C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin
      • C:\Program Files\Microsoft SDKs\Windows\v6.0\Bin
  2. On your development machine put the component in one of the folders under C:\Program Files\Microsoft SQL Server\90\DTS (default install location):
    • \Connections
    • \ForEachEnumerators
    • \LogProviders
    • \PipelineComponents
    • \Tasks

The component I created is a Pipeline Component so it belongs in the PiplelineComponents folder.  Once you have put it here you can add it to your toolbox by right-clicking on the toolbox, selecting “Choose Items”, select the SSIS Data Flow Items tab (in this case), and select the Error Details component.

This is really a summarization of this blog post by Matthew Roche that you can read for more details.

Friday, April 3, 2009

Error Handling in SSIS re-featured on SQLServerCentral

I am honored that my first ever published article, Error Handling in SSIS, was re-featured on SQLServerCentral today.  I don’t know how Steve selects articles to feature a second time, but I assume it was because they were popular.  Here are a couple of earlier blog posts about this article/subject:

Error Handling in SSIS Published

SSIS Error Logging Custom Component

Thursday, April 2, 2009

Update on my 2009 Goals

Since we are 1/4 through the year I thought I should give an update on my goals.  Overall I think I am doing okay, but I need to work toward them with more purpose.
  1. Learn SQL Server 2008, particularly policy-based management, resource governor, and service broker.
      Not a whole lot of progress here.  I do have it installed on my laptop, but I have not done much with it, nor have I bought any books.  I really need to get started.
  2. Write 1 Article per quarter for SQLServerCentral.
      Well, I did get an article submitted in the first quarter which will be posted early in the second quarter so I am on target, especially since I have the information for at least 2 more articles already, so I “just” need to write them.
  3. Record and submit 1 video per quarter for JumpstartTV
      Again I did get one done in the first quarter so I am on target.  I also have some ideas for others so it is all about getting them scripted and recorded.
  4. One Blog post per week
      Another one I am meeting with 19 posts in the first quarter.  I have to admit to this being a struggle for me.  I really want the posts to have good content and not just be announcements and recaps of meetings I have been to.  There is nothing wrong with those posts, but I’d like to be sharing something others can learn from more often.  I think that as I write more, it will get easier and better, not just to write, but also to find topics.
  5. Become more involved in leadership in my local user group (OPASS).
      I’m trying to do make the time to do this, but I need to be more purposeful about it.  I am trying to help out by pursuing speakers from within my network.  Of course, a bigger network would make it easier.  Time is limited and whatever I do try to do I want to do well.  Also I have become more involved in PASS as my friend Andy Warren (the OPASS president) is on the board of directors, so I’m trying to help out there as well.
  6. Speak at 2 community events.
      I have a submitted a session to SQLSaturday – Pensacola and will be submitting at least one session for the PASS Summit, so I am making some effort.  I do need to contact some of the other User Groups within driving distance (Tampa, Jacksonville) so I can get more experience.  Odds are I will put together another presentation for OPASS this year and at the next SQLSaturday – Orlando.
  7. Attend the PASS Summit
      I am working on this one.  I have spoken to my boss about possibly getting some funds for it, but, regardless, I plan on going.  As I already mentioned I will also be submitting at least one session.  This is probably the easiest one to do as all it takes is a little money and some vacation time.

What happens if you register for the PASS Summit and then your session is accepted?

I asked Andy Warren this because he is a PASS board member and if he didn’t know he’d know who to ask.  They do exactly what you would expect, they refund your registration fee using whatever method you used to pay.  Thanks Andy and Marcella Santoso at PASS HQ.

If your interested in why I asked, it is because there are discounts for registering early and, being frugal (cheap) and not confident that sessions I submit will be selected, I want to take advantage of the discounted registration fee.

Replaying a Profiler Trace Featured on JumpstartTV

The video, Replaying a Profiler Trace, that I did is being featured on JumpstartTV today.

JumpstartTV is a web site dedicated to professional development by providing specific 2-5 minute how to videos currently focused on SQL Server and .NET.

Wednesday, April 1, 2009

Why Speak at a User Group?

This post was going to be a plea for volunteers to speak at my local SQL Server User Group (OPASS), and, in reality, it still is, but as I began writing I thought I would share some reasons why I think you should consider speaking at a User Group, hopefully OPASS and why I did.

  1. Share your passion.  People will listen to anyone speak on any topic if they can tell the speaker is passionate about it.
  2. Become an expert.  There is no better way to become an expert than to teach someone else.  I spoke last year on SQL Profiler, which I thought I knew, until I prepared for my presentation.  Now I am an expert, but one who knows there is more to learn.
  3. Build your brand.  Steve Jones writes and speaks about this.  By speaking, you build your professional profile.  If you are willing to share your knowledge with “strangers” this can set you apart in your next job search.  Being a team player and a good communicator comes up in almost every job posting and interview, if you speak publicly you’ll have evidence to support your claims.
  4. Contacts, contacts, contacts.  In real estate they say it’s all about location, in the business world it’s all about contacts.  Not just for job searches, but for people you can contact for help on that issue you just can’t solve.  You may become that person for some, but you’ll also gain people who will be that person for you.
  5. Personal growth.  Public speaking is scary, but very satisfying, and you know what, everyone wants you to succeed, so our fears are usually unfounded.  Once you get started you’ll be more confident.
  6. Respect of your peers.  Don’t we all want that?  There is nothing I respect more than someone willing to take a chance.  No matter what people think about the presentation, they will respect you for daring to do it.

I have to be honest, I have only spoken twice on SQL Server (same presentation) and I was very nervous both times, but once I got started it just flowed and was over before I knew it.  Both times there were questions I couldn’t answer, but no one was upset by that because I didn’t bluff, I just admitted it.  As I said earlier, the audience wants you to do well, so relax and go for it.

If anyone is interested in volunteering to speak at OPASS, we do need a speaker for our next meeting which is in May, and to the best of my knowledge the rest of the year, you can contact me at corbett.jack@gmail.com and I’ll pass along your information to our president, Andy Warren.  We usually meet on every other month on the 2nd Tuesday, but we can be flexible with advanced notice, so if you are going to be in the Orlando area at another time and want to speak let me know and we’ll try to arrange it.