A couple of sites…I need to make a plan

So I put it out there that I was going to get certified and haven’t done it.  Thank goodness no one’s reading this blog nor holding me accountable.  Guess I better do that myself.

Set of exams purchased together: http://bsf01.com/Microsoft_Vouchers/MCSA_WinServer_SQLServer.aspx

These are the actual MCSA exams for SQL Server.  They include the 2008 MCTS upgrade exams:

http://www.microsoft.com/learning/en/us/certification/cert-sql-server-mcsa.aspx

Here’s the link to the Second Shot that MSFT is currently offering for the MCSAs:

http://www.microsoft.com/learning/en/us/offers/secondshot.aspx

Learning day

Yesterday was quite a day.  Spent the majority of it working with a tech from a very large shipping company.  They have several tools we use and we’ve been working with them for years.  We keep seeing the occasional transactions where the shipping cost on the order is $900 but when the order is transferred to invoice it becomes $50.  That’s not a good thing.

Spending the entire day with him was kind of painful, but there was a big payoff for me at the end of the day.  This is what I learned:

This guy has too much access to our systems.  He’s showing up in the warehouse and implementing upgrades, logging and who knows what, and we in IT don’t know about it.  I’m going to have to discover or create a policy for vendors.

Not all views are prefixed with a “v.”  I know, that’s pretty silly, but I spent 5 minutes looking at why the heck “SELECT TOP 10 * FROM SHIPWS_GOODS” yielded results but I couldn’t find the table in SSMS.  I should have queried sysobjects as soon as I wanted to know what that was.

A poorly defined table is bad practice.  The history table that logs the tracking number, invoice number and more for every shipment had no primary key.  That tracking number in the shipper’s world IS primary and unique, and there shouldn’t have been more than one row per tracking number.

Which brings me to the part I actually liked…how do we fix this mess?

The problem was a table with ~50K rows of data actually only had ~27K distinct tracking numbers.  Many of the tracking numbers were in the table 2 and 3 times, and what made it a little more challenging, some of those “duplicates” had different data in the rows.  They were nearly identical.  So how do I purge the duplicates and get all the data possible?  It was a mental exercise that I’m sure many DBAs have faced before.  I was initially trying to make it too hard, and must give much thanks and credit to an article on SimpleTalk that crystalized the entire problem and solution.

http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/

After reading the entire article I got to the the section entitled “New Techniques for Removing Duplicate Rows in SQL Server 2005” and that was my Aha! moment.  I’d seen but not understood the OVER and PARTITION BY functionality.  Right now, it makes perfect sense.  So this is what I ended up doing:

Quick temp table (SELECT * INTO HistoryTEMP FROM History)

TRUNCATE TABLE History

ALTER TABLE History ADD PRIMARY KEY (TrackingNo)

WITH DeDuplication AS (
SELECT  *
      , row_number() OVER ( PARTITION BY TrackingNo ORDER BY TrackingNo ) AS nr
FROM    HistoryTEMP
        )
INSERT INTO History
SELECT [CompanyCode] –this is an abbreviated list of columns
    ,[ShipDate]
    ,[TrackingNo]
    ,[COMPANY]
    ,[Country]
    ,[REFERENCE]
    ,[PONUMBER]
    ,[WEIGHT]
    ,[ImportedBillingCharge]
    ,[PickupDate]
FROM DeDuplication
WHERE nr = 1

The insert happened in a flash, and all I had left was some updating from my HistoryTEMP table to add some of the data.  I’ve essentially merged and de-duplicated data, and learned a lot to boot.

Next steps will include tightening the controls for consultants doing work for us!

Where am I going?

My son and I ran a 5K on Saturday.  A real 5K, with chips and bibs and a “cool” shirt for participating.  I thought it would be easier than it was, because I’ve given up a lot of lunches to go running this year.  However I’ve slacked off for the last five to six weeks, and my legs and body weren’t very happy to be running in the cold (33 °F) at 9am on a Saturday.

Kyle did pretty well, considering he hadn’t trained at all.  He came in at 24:38, 42nd place overall.  The picture of him crossing the finish line shows him just edging out a high school athlete!  I came in 222nd place with a 35:58.  I think I edged out some people pushing strollers.

Why’d I do the run without more preparation?  I figured until I just DID one I wouldn’t have a real mark to shoot for.  I needed know where I am and can set a goal on where I want to be.

Why would I post that terrible time here, and what has that got to do with my SQL experiences?  I still don’t have my MCITP.  My plan was to prepare for and take the 70-431 first and keep going from there (my work environment is still almost completely SQL 2005.)  I did study and schedule an exam, only to get sick that day.  Life seemed to get busy, and I’ve never rescheduled.  So I don’t really know where I am in my learning and abilities.  In my mind, until I take that exam I’m just kinda running around and not going anywhere.

Looking around the Prometric site this morning I see lots of opportunities this month.  So I’ll check the family schedule tonight and set an appointment before Christmas.  And I’ll post my results here no matter how I score.  Then I’ll know where I am and can plan on the future.

Date from a text message

I don’t know why you’d build a table this way, but someone did, and I wanted more information from it.  It’s a very small table having less than 1000 records in it yet.  Creating a similar table would look something like this:

CREATE TABLE #ProcessStatus (
    RecordNumber int NOT NULL,
    Status char(21) NOT NULL,
    LongMessage char(255) NOT NULL,
    iID int IDENTITY(1,1) NOT NULL,
    )
GO

INSERT INTO #ProcessStatus
SELECT 1, N’SUCCESS’, N’Process Run Date: 3/20/2010- Process completed with success.’
    UNION ALL
SELECT 2, N’SUCCESS’ ,N’Process Run Date: 3/31/2010- Process completed with success.’
    UNION ALL
SELECT 3, N’UNEXPECTED ERROR’, N’Process Run Date: 4/1/2010- Processing aborted due to unexpected error – see exception report.’
    UNION ALL
SELECT 4,  N’SUCCESS’, N’Process Run Date: 9/1/2010- Process completed with success.’
    UNION ALL
SELECT 5,  N’UNEXPECTED ERROR’, N’Process Run Date: 12/13/2010- Processing aborted due to unexpected error – see exception report.’
GO

So a couple of things jump out at me.  As long as there are no deletes, the RecordNumber will match the iID number so the need for 2 row numbers is lost on me.  The other odd thing to me: the message is storing a date in it but there is no datetime record.  I wanted to query the table and return the actual dates the process ran.

I don’t have visibility to the code that is creating the entries in this table so I don’t know that there aren’t more options than SUCCESS or UNEXPECTED ERROR but every message in the table starts with “Process Run Date: ” so I came up with this solution:

SELECT
     RecordNumber
    ,Status
    ,’Message’ = RTRIM(LongMessage)
    ,’Date’ = SUBSTRING(LongMessage,19,(CHARINDEX(‘-‘,LongMessage) – (CHARINDEX(‘:’,LongMessage) + 2)))
    ,iID
FROM #ProcessStatus
ORDER BY iID DESC

I’d never had to use CHARINDEX before, and I suspect it will come in handy in my future.  In this case, as long as the message always begins with the “Process Run Date: ” my SUBSTRING will start with the 19th character.  As long as the “date” is bookended by the [: ] and [-] then I’ll get the date.

Seems to me like the table could have been designed better, and perhaps it can be modified, but this was the quick and easy thing to do, and sometimes that’s enough.

Beginning

This is a beginning of something completely new and foreign to me.  While initially I’ll keep this private, the expectation I have for this blog is to make it available to my trusted friends and colleagues, for their insights, encouragement and criticisms.  From there the world at-large.

This entire adventure started yesterday because John Robel (Blog@JohnRobel) made it happen.  I’m pretty sure it’s because he’s a leader and mentor to me, but it also might be because he’s tired of listening to me talk about it.  Sometimes you need a not-too-gentle push to try something new.  I appreciate him very much.

I’m excited about the unknown.  I’m nervous that I’ll make mistakes.  But I’m confident I’ll learn from them and keep growing.

So here’s looking forward to the future imperfect.

ryan