Whirling, Transient Nodes of Thought

Remembering Y2k

2012-01-16T13:07:00.001-05:00

I just stumbled upon an old email I sent out to my fellow geeks on 2/25/99. The industry was full-bore into Y2k refactoring of all systems to prevent potentially cataclysmic consequences (purportedly anyways). That’s all any of us talked about. The email was in regards to a conversation I had late at night with my wife MaryAnn. Here are the details from that evening’s events:

Late last night (Feb 25, 1999) I was working on my home PC with my wife MaryAnn sitting on the bed behind me. I was pretty involved with what I was doing because of the importance of the software that I was installing. MaryAnn was making out a grocery list, asking me the occasional question about it as I worked. Here's the dialogue:

MaryAnn: "Oh, by the way, do we still want a humidifier? When I was at Wal-Mart the other day, I saw a humidifier for $19.99."

Boyd: "A humidifier for 1999???? Since when did anyone care what year a humidifier was supposed to be used in??? What the heck is it.....Y2K compliant or something???? What, are we gonna have a world-wide crash of all pre-Y2k model humidifiers??? That’s the stupidest thing I’ve ever heard of!"

Needless to say, MaryAnn looked at me with a "did you check your brain at the door?" look on her face. When I finally comprehended that she meant $19.99 and not the year 1999, we both had a good crack up for a good 10 minutes or so. I was crying.

I guess it's more obvious with some of us than with others that we've been spending way too much time slinging code.

Pivot Tables in TSQL: A Rant

2012-01-07T19:53:00.001-05:00

One of my biggest complaints in the industry to which I currently belong is the arrogance, ego and on occasion downright foolishness I see quite often when it comes to educating others on understanding what should be simple concepts (after a bit of explanation that is). I don’t mean that to be a blanket statement. Those attitudes are surely not prevalent everywhere. I know some incredibly intelligent folks who are very, very good at teaching others. But those negative characteristics are quite prevalent nevertheless. Here’s a perfect, textbook example of what I’m talking about. Pretend your resident n00b programmer asks someone experienced what an interface is. Here’s the status-quo answer given:

“An Interface is a Contract”

Huh? Wha? What the heck does that mean? And yet that’s what I’ve seen in many a book. It’s the answer provided me by interviewees in many an interview I’ve given (most times with no further explanation on usefulness). And it’s also the response during many a conversation with someone who knows what an interface is. But too often I’ve seen those new to programming shake their heads up and down eagerly indicating they understand that statement completely. But in reality, they don’t. They’re clueless. Their code shows it quite clearly. And only when I pull them aside and get them to confide in me does the Truth come out: They’re freaking clueless. And rightfully so. I’ve counseled many people over the years on what an interface is and the virtues of them. So many never understood them until I explained. When I explain in non-geek-textbook-speak, the light goes on and they’re finally reconciled on the beauty of interfaces.

But this isn’t about interfaces. We’re wanting to talk about Pivot Tables today.

What the Experts Say about Pivot Tables

Here’s what they say:

“A Pivot Table is a transformation of flat data into something more clear.”

Um, yea. Clear. Right. Speaking of clear, that explanation is clear as mud. Even Wikipedia’s description doesn’t help the n00b who’s desperately looking for answers (though a person could probably pull the answer out of their example). That is if that n00b even knew that they need to find out what a pivot table is. The closest they come to answering the question is the word “rotation” used in the definition. Good effort, but still not there. The closest I’ve seen out there to the Truth is the WhatIs (TechTarget) definition. But still, not quite very good.

And don’t get my started on the nonsense that exists in the blogs I’ve seen. Though I’ve surely not scoured the entire net, it’s the same nonsense copy & pasted from textbooks.

What is a Pivot Table?

So, here’s a lesson in Pivot Tables. Hopefully it’ll also be a lesson on how to properly teach people on a subject. To start, here’s my own personal definition. This is for those who’ve no clue what a pivot table is:

“A Pivot Table is a table in which row data from one table has been “rotated” or “pivoted” into the place of column related data in another table, or vice-versa.”

A Solid Example of a Pivot Table

That may not quite make sense just yet. So let’s take it a step further: What would a definition be without examples? Here’s a table with some data. The data has not been pivoted yet:

Table 1

Notice the redundant data. We’re pulling all permutations of Cost Code to Component percentages. Now imagine a huge list of Cost Codes with an even larger list of Components! That would be one long and butt-ugly report, wouldn’t it?

A Pivot Table takes the data of an original resultset (found in the Rows), yanks it out and turns that data into categories of data by turning that data into Columns:

Diagram 1

Notice in the diagram we’re taking the data categorized underneath “DataColumn”, sliding it all out of the table and rotating it to instead become column headers. This is the “pivot” in “pivot tables”.

So now let’s use real data. Taking a look at the data in Table 1 above, it seems that we’d want to aggregate (group, add up) the percentage data that’s common to Components and show them by Cost Codes. For those who are new, here’s the key to your understanding: We’re going to pull the “Component Description” column out and pivot that data into “columnar” data instead of row data.

So, let’s first take a look at the finished pivot table, then we’ll go into the explanation:

Table 2

Do you see what we’ve done? We’ve taken what was the return result (the data returned from the previous query on the table above) and we’ve changed that resulting percentage data to be based on Cost Code. A much better report that’s must easer on the eyes and brain that the previous. We’ve “rotated” or “pivoted” what was vertical data in the table to now being horizontal data. In doing so, we were able to effectively aggregate (group) the redundant Cost Code data. “How cool is that?” (Mark Aitkenhead, circa 2012 )

Diagram 2

The Code

Now let’s take a look at the SQL.

The standard example of using pivot tables in TSQL typically shows pre-knowledge of the column data that one would want to use. Take another look at Diagram 1 above. In other words, most examples assume you know ahead of time all the data that’ll show up underneath “DataColumn” and would therefore be able to hardcode it into a SQL statement to create your pivot table:

Hard-Coded Pivot Selections
 Select Data1, Data2, Data3, Data-n
From...
 

Snip 1

Being severely allergic to hard-coding, I refuse to do so. You should also refuse. Some of the most common famous last words in programming are, “It’ll never change.” That statement is the sign of either a n00b or a fool. Avoid hard coding and magic numbers/values at all costs.

Here’s the TSQL solution for pivoting your data without knowing ahead of time the number or names of the columns that’ll be pivoted. In our examples above, that would be the “Component Description” data. First, we’re going to create a sproc that dynamically builds the list of Component Description columns we’ll be selecting:

Column String Builder
 Create Proc [dbo].[upGetComponentSelectionStringForPivot]
    @DivisionKey int,
    @Components Varchar(1000) Out
AS
BEGIN
 
    SET NOCOUNT ON;
 
    Declare @Com Varchar(1000)
    Select @Com = ''
    Select @Com = @Com + '[' + c.Description + '], '
    From Components c
        Inner Join CostCodeComponentXRef x On c.ComponentKey = x.ComponentKey
    Where x.DivisionKey = @DivisionKey 
    Group By c.Description 
    Set @Components = SUBSTRING(@Com, 0, Len(@Com))
 
END
Go
 

Snip 2

Of course, your overall SQL structure will surely look differently. This SQL is based on a live project. But you should get the gist of what we’re attempting to accomplish here. We’re building the column list that’ll be used in a Select statement in another sproc. And now here is the final SQL that’ll render us our data pivoted:

Pivot Sproc
 Create Proc [dbo].[upGetCostCodeComponentPivot]
    @DivisionKey int
AS
BEGIN
    SET NOCOUNT ON;
 
    -- First, create the column headers we'll be building the pivot on.
    Declare @Components Varchar(500)
    Exec upGetComponentSelectionStringForPivot @DivisionKey, @Components Out
   
    -- If we have no columns to select from, then bail. 
    If Len(LTRIM(RTrim(@Components))) = 0
         Return -1;
 
    -- Next utilize the pivot column string in the overall query.
    Declare @Query Varchar(2000)
    Set @Query = 
        'Select [Cost Code Description], ' + @Components + '
        From
        (
            Select cc.Description [Cost Code Description], c.Description [Component Description], x.PercentComplete [Percent Complete]
            From CostCodes cc
                Inner Join CostCodeComponentXRef x On cc.CostCodeKey = x.CostCodeKey
               Inner Join Components c On x.ComponentKey = c.ComponentKey 
            Where x.DivisionKey = ' + Cast(@DivisionKey As Varchar(25)) + '
        ) As Source
        Pivot
        (
            Min([Percent Complete])
            For [Component Description] In (' + @Components + ')
        ) As ThePivot'
       
    Exec(@Query)
 
END
Go
 

Snip 3

Again, you’ll need to tweak the SQL (within the red text) to your specific selection needs. But overall, the “Pivot” keyword, coupled with the appropriate selection criteria will rotate your data accordingly.

Take your time in looking at the code. And be sure to comment and let me know if you need any help or have any questions.

Unlocker – A Great Utility for Dealing with Stubborn Files

2011-12-22T23:30:00.001-05:00

Yes, it’s been quite a while since blogging. I’ve been doing my hair.

I have an old archive drive that has some old code libraries and db’s I need to resurrect. When I went to copy the SQL Server database files over (*.mdf & *.ldf), I surprisingly received error messages telling me I don’t have “permissions” to the file.

Um, f’real? I mean, once in a blue moon I’ll get a lame message like that from Windows. But this time, it was very unusual. A database I’d built by hand and used on insanely large enterprise levels? Come on.

At this point I’ve no clue why the negative temperament. However, I didn’t really care. I needed the files and needed them quick. So the first thing one does is check out what processes have a hold on my files. A quick search via Process Explorer didn’t turn up anything. So, I then go to the security settings of the files and have some interesting text to read I’ve never seen before in all my travels:

"You do not have permission to view or edit this object's permission settings.”

Now things *really* don’t make sense. I’m quite obviously the proverbial god on my machine. There’s no higher authority (other than Winderz itself). I can neither view the file’s security settings nor take ownership of the files.

Enter Unlocker. A slick little, humble utility that totally P’wned whatever had an invisible hold on the files. By humble I mean, it just does its job and goes home. That’s it.

Upon startup, it’s no-nonsense interface just pulls up a file selection dialog:

At this point, the creator, Cedrick Collomb, assumes that if you’ve taken the time to find his utility, download and install it, that you know what you need to do next. So I select my first file (one of four having problems), and get the next dialog:

I love it. “However Unlocker can help you deal with this object.” A totally confident statement that Percy on Nikita would make to his Cleaner. The following scene, there’s dead bodies everywhere. I’m going to try to use that moniker in some code soon.

Since I want these files, I select “Move”. It again brings up the selection dialog, but this time it’s looking for a destination folder. After selecting where I want the file moved to, within two seconds, I get the final dialog:

Click OK and Unlocker is gone. Job done.

Widgets just don’t get much better than this. Simple. Discrete. Does its job and gets outta dodge.

Great job Cedrick.

Technorati Tags: SQL Server,Power Tools,Security,Utilities,Windows,Locked Files,Explorer,Permissions,Cedrick Collomb

Skype Rawks!

2011-06-02T13:38:00.001-04:00

I’m using Skype for the first time per a new client requirement. I just got off my first call. I must say the quality was incredible. I’m very impressed.

I guess we’ll all have to tune in to see how things go now that Microsoft owns them. The next year or so may very well be interesting.

The XKCD Guy is a Genius

2011-02-03T09:28:00.001-05:00

I just love that guy:

http://www.xkcd.com/835/

Technorati Tags: Humor,Geek

The number 58?

2010-11-10T12:01:00.001-05:00

Today's date, 11/10/10, is 111010 which in Binary is 58. I wonder the significance in that number and why on earth I even noticed this???

Technorati Tags: Geek,Binary

Migrating a Shelveset from One Branch to Another in VSTS 2010

2010-10-18T18:46:00.001-04:00

There’s a very nice list of utilities the Visual Studio 2010 Power Tools brings to the table. It would definitely benefit any developer using VSTS 2010 to get the power tools installed and bring themselves up to speed on the capabilities available.

However, there’s one particular nugget that is very helpful when it comes to projects that have a branching strategy in place. And that’s migrating shelves from one code branch to another in your repository. For more on proper branching strategies, see the Visual Studio 2010 TFS Branching Guide on CodePlex.

Some of the power tools available are only available from the command line out of the box. This one falls into that category. So to start with, bring up a Visual Studio command prompt. The easiest way is to hit the Windows key and type “command” in the search box:

To see your available options and the nomenclature of the unshelve command, type in “tfpt unshelve /?”:

Pretty simple and straightforward. But here’s a snip for you to copy/paste into the command line, just replace the tokens accordingly:

tfpt unshelve “<your shelveset name>”;<TFS Username> /migrate /source:”<serverPath>” /target:”<serverPath>”

Of course, I included the double quotes because more often than not, shelveset names as well as server paths have spaces in the text.

Here’s a sample with tokens replaced:

tfpt unshelve “Latest - Pre v1.0 to v2.0 Branch Migration”;joedeveloper /migrate /source:”$/trunk/v1.0/Source” /target:”$/trunk/v2.0/Source”

It’s that simple. However, there’s one caveat: You may receive an error telling you, “Unable to Determine the Workspace”. The easiest way around this is to run the unshelve command directly from one of your local directories that’s mapped to your local drive. In my case, I typically just run it from the source directory that’s mapped to the source server path. So in the above example, I would navigate to the “C:\Projects\MySoftware\trunk\v1.0\Source\” directory from the VS command line before running the unshelve command.

From there, you’ll receive dialog prompts similar to those in Visual Studio asking you to perform a merge. Follow the instructions, and you should start seeing confirmations in your command window for every file merged:

If you have local changes in the target directory, you’ll need to resolve them manually. From there, your merge of your shelveset from the /source to the /target will be complete.

Technorati Tags: Visual Studio,TFS,Power Tools,Programming,Development,.NET,DotNet

Windows Phone 7 Developer Tools – Cannot Run in Compatibility Mode on Installation

2010-10-05T10:16:00.001-04:00

I ran into an interesting problem recently while installing the Windows Phone 7 Developer Tools (download here). Upon execution of the setup, I received the following:

So naturally, the first place I went to look was on the Compatibility tab of the setup exe’s Properties. Lo and behold, here’s what I found:

I have to say, I’ve never run into that issue before. Why would the compatibility checkbox be disabled?

Digging a little deeper by heading out to the readme designated in the error window above, here’s what section 2.1.10 says:

Visual Studio Setup cannot run in program compatibility mode. Ensure that the compatibility mode setting is not enabled system-wide or for the Visual Studio Setup application.

Just below that are some registry hacks that fixed the issue for me (you may have to reboot after changing):

Check Program Compatibility Assistant registry settings

Compatibility mode could have been set by Program Compatibility Assistant on earlier failed or canceled attempts to run Visual Studio setup.exe.

Click Start, and then click Run.
Type regedit, and then click OK.
Browse to each of the following keys and delete any value that refers to setup.exe in the Visual Studio install path:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Compatibility Assistant\Persisted
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers

And there you have it. The compatibility mode checkbox will now be disabled so you can uncheck the box.

Technorati Tags: Visual Studio,Windows Phone 7,Hacks,Compatibility,WP7

Debugging Silverlight Apps in Firefox

2010-07-27T13:20:00.001-04:00

Here’s a simple little tweak to your Firefox settings that’ll allow you to debug your Silverlight apps in Visual Studio:

Close all instances of FF except one or open a new one
Type in “about:config” in the address bar
Accept the warning if it shows up
Filter down to “dom.pic.plugins.enabled.npctrl.dll”
Double-click to set the value to “false”

Voila! VS Debugger will now stop on your breakpoints accordingly.

From the Desk of Steve Jobs: iPhone4 to Utilize Flash!

2010-06-09T09:29:00.001-04:00

It’s true!

http://voppa.posterous.com/19883514

Unit Testing Internal Members – A Reminder

2010-05-25T15:47:00.006-04:00

Beginning in .NET v2.0, there’s a very easy method of allowing internals in your projects to be seen by external testing projects. Simply decorate your type with the InternalsVisibleTo attribute and, badda-bing, you’re in bidness. Or, an easier method of doing so is to decorate the entire assembly by navigating to the AssemblyInfo.cs file of the Visual Studio project containing the internal members you’re wanting to test. Then add the following assembly attribute to the file:

[assembly: InternalsVisibleTo( <your fully qualified test project name here> )]

Example:

[assembly: InternalsVisibleTo( "MyTestProject.Test" )]

Now, you should be able to see your test project available to select for addition of private selectors from within your internals classes:

The accessor file will automagically be added to your test project. Now you’re ready to test the internals directly as they’ll now be accessible through your project’s API.

Visual Studio 2010 TFS Mapping Appends “_1” To Project Folder

2010-05-21T11:33:00.001-04:00

Very weird anomaly I ran into yesterday.

I checked in a new project with associated code to TFS source control. Upon checkin, TFS insisted on appending “_1” to my project folder’s name. It wouldn’t allow me to change it anywhere before checking in.

After the checkin, I hunted around to see what I could find. Another mapping was added to my Workspace “Working Folders” section that had a direct link between the new Source Control Folder and my Local Folder. BTW – My local project folder name did *not* have the “_1” appended. It was as I created it.

TFS Solution: I actually had to have someone else remove the “_1” from the project folder directly in Source Control and check in the change. He did do it from the command line though, which I didn’t attempt. Maybe that had something to do with it. I doubt it though as the UI is running command line commands under the hood. I just wasn’t in good graces with TFS for some reason and couldn’t make it work.

Local Solution: First, I removed the erroneous mapping from my working folders. Just select the mapping and click “Remove”:

Then, I reestablished the project folder’s mapping directly in Source Control. Back in bidness.

Please comment if you happen to have insight into this little gremlin.

Authorization Manager (AzMan) Connections to SQL Server

2010-05-19T14:41:00.007-04:00

Strangely, it was rather difficult to find any good examples of the proper connection string nomenclature for connecting AzMan to SQL Server (notice I said *good* examples). Even the MS documentation conveniently excludes examples (surprise, surprise!).

So with a little trial & error, I finally figured it out. Here are the two I’ve come up with:

Trusted: “mssql://Driver={SQL Server};Server={<servername, “local” or “.”>};/<database name>/<AzMan store name>”

Untrusted: “mssql://Driver={SQL Server};Server={<servername, “local” or “.”>};Uid=<user id>;Pwd=<password>;/<database name>/<AzMan store name>”

Examples:

mssql://Driver={SQL Server};Server={MySqlServer};/MyDatabase/ClientABC

mssql://Driver={SQL Server};Server={.};Uid=MyId;Pwd=MyPassword;/SomeDatabase/SomeOtherAzManStoreName

One caveat: Under my current setup with our client’s VPN, I wasn’t able to get Windows Auth to work. When attempting to use domain ID’s, AzMan would throw errors when attempting to connect:

mssql://Driver={SQL Server};Server={.};Uid=MyDomain\MyDomainId;Pwd=MyPassword;/SomeDatabase/SomeOtherAzManStoreName

Instead, the connection had to be setup with SQL Server auth, using a user that had been created locally on the SQL Server instance.

I’ve also created a regular expression to match the connection string if it’s being passed in from outside sources for multi-tenancy purposes:

^mssql://Driver={SQL Server};Server=\{[\w-.]*\};(Uid=[\w]*;Pwd=[\w-*]*;)*/[\w-]*/[\w-]*

I’ve added the expression to Grant Skinner’s very nice online Regular Expression Builder & Tester library.

My New Favorite Rapper

2010-05-19T10:18:00.003-04:00

Ok, so though some of his songs are off-color and I don’t listen to them all, many of MC Frontalot’s songs are great! My current favorite is Charity Case. I’m just crackin’ up here!

I listen to him on GrooveShark, that way I can filter out the smut from the good stuff.

Sorry for not buying your CD’s Front. But if you’re ever in my neck of the woods, I’ll come see you and make up for it with dinner that’ll add up to more than the cost of a CD anyways.

Back in the Blogging Saddle

2010-05-18T13:26:00.004-04:00

Unfortunately, something happened at my ISP (1 and 1) that made my blog blow up. No clue at this point what it was. But despite my best efforts and time, I couldn’t get it to work again.
So, I went out to find that the guys at blogengine.net had come out with a new version. I’ve been using their engine for a while and love it! But when I went and got the latest version up and running, alas, it would neither work with my old theme nor would it work with any other themes that are out there for the engine. Literally none of the themes I found would work with the new version other than the vanilla defaults that came with the engine. So instead, I went with the next best and quickest thing I was able to find and still use my own custom URI for free: Blogger. Yes, there are other options out there that are much geekier and allow more flexibility. But again, time was an issue for me.
So, here’s the new blog. I’ll be enhancing it as time goes on and will hopefully be able to port over all my old posts from my previous blog. But in the mean time, this’ll be an experiment until I can get it to a manageable state I’m pleased with.