Archive for the ‘SQL Server’ Category

T-SQL Tuesday #14 – Resolved to do better

January 11, 2011 2 comments


This month’s #TSQL2sDAY is being hosted by Jen McCown (Twitter | Blog). The topic this month is about techie resolutions for this coming New Year. 

Review – You suck!

I am my worst critic and I can usually talk myself out of most things.  One of the hardest things for me to do last year was blog.  I still consider myself a newbie to the whole blogging thing.  I haven’t found my niche or what I am trying to convey.  I am still feeling a bit random about what to post.  My first blog post was my last year’s 2010 resolution [Blog].

Reflect – It was a year ago?

Wow, can’t believe a year just passed. 

I just posted my 2010 Review [Blog] and if I was actually being graded on these, I would be taking summer school. 

Resolved to do better

I am not so much into setting New Year’s Resolutions and as you can see from my 2010 review (link above) I did terrible.  This year the plan is to be as vague as possible to achieve higher scores (not really).  Instead of resolutions, I shall call these goals.

1. Take Microsoft SQL Server 2008 Cert Exam 70-432.  My primary reason is to keep up my skills and learn more about SQL Server areas that I don’t necessarily use in my day to day job.  Last year I set a goal for myself to take the 70-431 and passed it in July 2010 [Blog].

2. Participate in all T-SQL Tuesday Blog posts in 2011.  I participated in 3 events in 2010.

T-SQL Tuesday #08 – Lifetime of learning

T-SQL Tuesday #12 – Why are DBA Skills Necessary?

T-SQL Tuesday #13 – I don’t know what I want, but I want it

3. Write at least 1 SQL Server Blog post a month.

4. Volunteer\Help at SQL Saturday #68 in Olympia (April 2011).   If I am able to attend PASS in 2011 (3rd timer), I will volunteer there as well.  Hope my boss approves budget.

5. Continue to learn SSIS.  I worked on two projects this last year using SSIS, which I really enjoyed and hated at the same time.  It’s funny looking back at the first project that I built.  Oh, please nobody look at it.  HAND IN FACE.

Better planning, being more organized, and continue to grow as a DBA


T-SQL Tuesday #13 – I don’t know what I want, but I want it

December 14, 2010 Leave a comment


This month’s TSQL2sDAY is being hosted by Steve Jones (Twitter | Blog). The topic this month is around “what the business says is not what the business wants”.

Have you heard this one before?  I am not sure what I want, but I want it.

I am currently involved in a project where we are upgrading/moving from an old in-house created application to a new in-house created application.  This involves the business analysts working with the other department getting all of their business needs.  Don’t get me wrong you need the input from the people that are going to be using the system or it will fail.  However, if you don’t know what you want, how can we deliver that to you.

I think what I am trying to convey is we do need to interact with the business users to see what they really need and give them those things that will help them do their job, which helps the company, which helps us stay employed.

I am thankful for a strong business analyst group in our department.  They know more about the business process then the actual users do.  They are able to anticipate with the user needs and work with development to give them those features.

Categories: 2010, SQL Server, T-SQL Tuesday

Beware of the Dark Side of negativity

November 22, 2010 Leave a comment

I read an interesting article the other day about dark side of negativity and pettiness.  The jest of the article was maintaining a positive attitude during stressful times.  The article was in my wife’s ASRT Scanner magazine written by Peter Rath, M.P.A., R.T.(R).  (Article is unavailable to link to, not online).

The author goes on to say that it is easy when things aren’t going right or in a stressful situation to say something that you will regret later.

Reminds me of the scene in Star Wars – Return of the Jedi where the Emperor is trying to turn Luke to the Dark SidEmperor Luke and Darth Vadere.  You too must resist the draw from “The Dark Side”, The Dark Side of negativity.  Oh, I feel the anger building up in you, let it out, let it turn to negativity.

I think we all have been in stressful situations at work.  A user complains about a reporting taking a long time to run, to a server/database crash.  The stress can escalate when a solution is needed right away and an answer is not known right away.  How we handle this can tell a lot about you as a person.  These are the situations where we can shine and be that Rock(star) or be the “Go-To Guy or Girl”.

I leave you with a quote from Jedi Master Yoda. “But beware. Anger, fear, aggression. The dark side are they. Once you start down the dark path, forever will it dominate your destiny.“

What do you do to help you in stressful situations?

T-SQL Tuesday – Why Are DBA Skills Necessary?

November 2, 2010 1 comment


This month’s topic is hosted by Paul Randal (blog | twitter), and the topic is “Why are DBA skills necessary?”

Just like any trade there is a specific level of skill necessary to do the job.  Just like an auto-mechanic there is training to do the job.  You wouldn’t want just anybody working on your car, especially if it is a newer one with all the computer modules etc. 

There are some basic things that a technical people can do in SQL Server, like changing a flat tire.  You don’t need to be a rocket scientist to change the tire.  You do need to have a jack, wrench, some brute strength, and the knowledge where the spare is located, most people can change the tire.  If you want to change the oil the on your car you need to be a little more technical.  You need to know what type of oil you need and the oil filter, and location of the filter. 

I believe that there are skills necessary to manage a database.  As much as software vendors like to say there is no "Set it and Forget it" feature in SQL Server.  If you forget it, it will bite you and usually where the sun don’t shine. 

In my previous job I worked in technical support for a software company.  I would get a call once a week from customers where their Transaction Log file filled up their C Drive (insert laugh here), and they had no database backups (insert tear here).  This is DBA 101 stuff, actually it is Business 101, but most of the companies I dealt with did not have a DBA on staff.  It was generally the person closest to the server. 

In those cases and most of the other issues that I dealt with if they had someone with the basic SQL Server knowledge it could have prevented 99% of their issues.

Bottom line: I feel that a DBA is necessary for the well being of your companies PRICELESS data.

Requesting SSIS Help

November 2, 2010 2 comments

Data Import from log/text file (poorly formatted)


I have been given the task to import some data logs from our firewall system into SQL Server for reporting purposes and to help keep data longer (also for reporting) as the firewall system database needs to purge data more regularly.


The fun part is the firewall software uses PostgreSQL as its database and I have not found a way to export the data out and into SQL Server.  So that leaves me to try and import the daily logs that it creates. 


Sample data:

1120557876,,9087042812EBD,1006,1143243492,1,0,6,SMTP-Inbound-SlimeWarner-00,smtp/tcp,,1163,,25,,0,,0,Slime Warner,Trusted,590,0,0,,SMTP-Incoming.GUS.1,,,,header=Date: Sat\, 4 Sep 2010 11:30:01 +0430,rule_name=Date:*,,,,,tr,ProxyAllow: SMTP Header,,2010-09-04 00:00:10


I created a simple database on my SQL Server called Firewall and created a table similar to the table in the firewall software. (I know bad table)


USE [Firewall]






CREATE TABLE [Firewall].[dbo].[cluster_traffic] (

[sid] NVARCHAR(50),

[cluster] NVARCHAR(50),

[sn] NVARCHAR(50),

[tag_id] NVARCHAR(50),

[raw_id] NVARCHAR(50),

[disp] NVARCHAR(50),

[direction] NVARCHAR(50),

[pri] NVARCHAR(50),

[policy] NVARCHAR(50),

[protocol] NVARCHAR(50),

[src_ip] NVARCHAR(50),

[src_port] NVARCHAR(50),

[dst_ip] NVARCHAR(50),

[dst_port] NVARCHAR(50),

[src_ip_nat] NVARCHAR(50),

[src_port_nat] NVARCHAR(50),

[dst_ip_nat] NVARCHAR(50),

[dst_port_nat] NVARCHAR(50),

[src_intf] NVARCHAR(50),

[dst_intf] NVARCHAR(50),

[rc] NVARCHAR(50),

[pckt_len] NVARCHAR(50),

[ttl] NVARCHAR(50),

[pr_info] NVARCHAR(50),

[proxy_act] NVARCHAR(50),

[alarm_name] NVARCHAR(50),

[alarm_type] NVARCHAR(50),

[alarm_id] NVARCHAR(50),

[info_1] NVARCHAR(100),

[info_2] NVARCHAR(100),

[info_3] NVARCHAR(100),

[info_4] NVARCHAR(100),

[info_5] NVARCHAR(100),

[info_6] NVARCHAR(100),

[log_type] NVARCHAR(50),

[msg] NVARCHAR(255),

[bucket] NVARCHAR(50),

[update_time] [datetime] NULL



The problem that I am running into is with the [info_] data field.  Some of the data has commas in it and this is causing problems when trying to import into SQL Server.


I initially used the SQL Server Import and Export Wizard in SSMS and this is how the data looks when I preview.  Some of the data is jumbled and moved over to the wrong column.  The highlighted shows the problem.





Is there a way to import this data correctly through SSIS with some sort of pre-cleanup?




Categories: 2010, SQL Server, Uncategorized

Have you heard “Don’t be this guy”?

October 30, 2010 Leave a comment

Don’t be this guy

Take a moment and read Tim Mitchell’s [Twitter | Blog] blog post “Don’t be this guy” from last year.  Basically he writes about his experience at a previous summit where he did not really take advantage of the networking opportunities at PASS and really regretted it afterward.  It is really a great read and it home with me.

Unfortunately, I did not read this until after my first PASS Summit last year.  Sad to say, I was that guy last year.  In reading the comments of his blog post, it looks like there were other people just like Tim, that were that guy as well.

This year, I am not going to be that guy.  I really want to meet a lot of the people that I follow on Twitter and who’s blogs I read on a regular basis.  I am going to be somewhat of a summit stalker.


Get yourself out there.

To help with the initial conversation ice breaker I had some Moo cards made up that I plan to distribute out at PASS. Not so much of a business card, but my social contact info (Twitter, email, LinkedIn, and Blog) “Just for fun”.  I have 50 of them, so my goal is to distribute all of them during PASS.  I think they will make a pretty interesting and fun conversation topic.

Events I am going to do.

Monday : I am going to participate in the PASS 2010 Photowalk [Follow Twitter tag | #passphotowalk].  This is put on by Tim Ford [Twitter | Blog] and Pat Wright [Twitter | Blog].  Find out more about it here.

Monday night is the Welcome Reception and Quiz Bowl (6:30-8pm) and SQLServerCentral opening night party (8pm-10pm).

Tuesday : Birds-of-a-feather (BOF) Lunch (11:45am-12:45pm) and the non PASS sponsored event Inappropriate PASS Session party put on by Jeremiah Peschka [Twitter], Jen [Twitter], and Kendra Little [Twitter].  Should be fun.

Wednesday : WIT Luncheon and Panel Discussion (11:30am-1:30pm) and the Microsoft Appreciation Party at Gameworks (7pm-10pm)

Thursday : Chapter Lunch (12pm-1pm)

Please check out SQLPASS’s website for full details on all the events.  Be sure to follow #sqlpass on Twitter for any impromptu events as well.

I am ready to get my SQLPASS on!  Are you?

Categories: 2010, PASS2010, SQL Server, Training

Passed 70-431 – late to the party

August 2, 2010 2 comments


One of my goals that I set for myself at the beginning of the year was to take a Microsoft Certification exam.  I eyed the 70-431/2005 exam mainly because I have been working with SQL Server 2005 for several years now and felt comfortable with this version.

In a previous post I discussed what books I used for studying for the exam, they’re all good, but for me the biggest thing was getting my hands dirty.  I setup a test machine at home where I could try and work through a lot of the features that I was less familiar with (like End Points and Service Broker).  This helped me get a better understanding of those features.  For me this is how I learn.  If I read it and don’t actually do it, I won’t understand it.

I finally got serious this last month and arranged my exam for July 31st.  It was set and there was no turning back.  I think that I get a little more serious when there is a deadline or goal to accomplish.

Test Day

On the test day I slept in and had a nice breakfast.  I did some last minute cramming on XML and End Point (yuck), and did a last minute practice test.  I shouldn’t have done that.  I failed miserably, let the self-doubt begin.

I arrived an hour before scheduled test and spent the time studying in my car.  I finally went into the testing site and took the test.  You know where all that old computer technology goes, it goes to these testing sites. 

Computer Testing

I took my time during the test and did not go back and review any answers.  Once I hit the next button my answer was stuck in stone.  I don’t like going back and reviewing my answers, this causes me more self doubt.  I say let it ride.

Got to the last simulation question hit the next button and then the big finish button popped up.  I took a deep breath and clicked.  It was like an eternity, I could hear the computer grinding and churning.  The results came up on the screen, but I didn’t initially see it.  I saw the possible score 1000, the passing score 700, and then my eyes finally found my score.  Bada Bing! Passed!  Felt like the world was lifted off my shoulders. 

Walked up to the front desk trying not to show my joy and got my results.  Went out to the car and sent a couple of texts to the wife and drove home.  To celebrate I took the family to Red Mill Burgers in Ballard.  Best burgers EVA! My plug for the community.  Also, I like good food.

New Goals

Next up 70-432/2008.  I am initially planning on late October – Mid November to take that test.  Over the weekend I wiped my lab computer clean and started a fresh build for my SQL 2008 labs.  Let the party begin.  Wahoo!  I am certifiable.

Categories: Certification, SQL Server