Caught flat-footed (again) by a NULL

It's common knowledge there are some curious (and occasionally head-scratching) properties of NULL in MSSQL. For instance, if a statement aggregates a nullable column, NULL values are excluded from the calculation. Given a table...

AddressID	StreetAddress	ApartmentNumber	City	ST
1	4432 Ash Street	Apt. 12	Brownsville	TX
2	3417 12th Ave	NULL	Kalamazoo	MI
3	46961 117th ST NE	NULL	St. Paul	M

...the query "SELECT Count(ApartmentNumber) FROM Address" will yield a count of 1, rather than 3.

Also, comparing two NULL variables to each other without an ISNULL operator will result in false (even though they contain identical values).

DECLARE @Var1 int
DECLARE @Var2 int
SELECT @Var1 = Column1 FROM Table WHERE (no match condition)
SELECT @Var2 = Column1 FROM OtherTable WHERE (no match condition)
IF @Var1 = @Var2
SELECT 'True' --ain't never gonna happen
ELSE
SELECT 'False'

And, oh yes, if

SELECT @Var = Col1 FROM Table WHERE (no matchcondition)

doesn't match a record (which, I suppose by definition it won't...), you might expect @Var to be assigned a NULL value. In fact, however, @Var holds whatever value it held before the SELECT statement, just as if the statement had never occurred (insert music from the Twilight Zone).

Anyway, aside from these better-known NULL behaviors, I stumbled across another not-so-intuitive quirk yesterday.

Assuming Table2's ColumnOfInterest field is nullable, the statement

SELECT * FROM Table1 WHERE SomeColumn IN
(SELECT ColumnOfInterest FROM Table2)

will return zero results of there are any NULL values in the subselect result, even if there are matching values. The lesson here: always scope the subselect query in situations like this to exclude that persnickety NULL.

Configuring a TFS BuildProcessTemplate to generate MSI setup files

Team Foundation Server uses MSBuild to create deployable assemblies. However, MSBuild, even the 2010 version, does not support the creation of MSIs from .vdproj setup install projects despite forum requests and chatter going back to 2005 (which I read initially with no small amount of frustration).

If you attempt to use TFS in its default configuration to build MSI files you will likely receive the error: The project file "SetupProject.vdproj" is not supported by MSBuild and cannot be built.

Fortuantely, there's a way to branch within the default TFS BuildProcessTemplate and invoke Visual Studio to do this particular job for you. The build PC will need its own copy of VS installed so we can call out to DevEnv during the build process. Once the build PC is correctly configured (VS, all necessary dependent DLLs, appropriate authorities set, etc.) then we can turn our attention to the BuildProcessTemplate.

Begin by making a copy of the \BuildProcessTemplates\DefaultTemplate.xaml file, naming it MSITemplate.xaml and checking it into source control in the same directory. Open this template and scroll about two-thirds of the way down until you find the section which deals with compilation, like Figure 1.

Figure 1


Figure 2

What we're going to do is modify this so that it tests the name of the project being built. If that project is our .vdproj, we'll invoke DevEnv instead of MSBuild. Figure 2 shows resulting workflow. At the end of the process, we'll need to find the MSI files that DevEnv generated (it places them in a different folder by default) and copy them to the Drop folder, but let's hold that thought for a moment.

To create a workflow that looks like Figure 2, drag an "If" Control Flow activity from the toolbox into the "If Local File Exists" condition. Set its condition property like this, only substitute your own MSI setup project's name. I've chosen to test for a specific project name since the filename extension ".vdproj" isn't included in the localProject variable, otherwise I'd have made this generic for all such file type suffixes. However, if all of your MSI setup projects contain a key phrase (like "Setup") you could test for just that string instead.



Then, move Microsoft's "Run MSBuild for Project" task into the Else condition of the If task we just created.

In the "Then" condition of our new "If" task, add an Invoke task and set its Filename and Arguments properties to DevEnv with the indicated Arguments. Note specifically that this call is not made to DevEnv.exe, but rather to DevEnv.com, which is its command-line interface.




And that takes care of the primary issue. If we change our build definition and specify that it use this BuildProcessTemplate, the MSI will be created by VisualStudio.

However, we still need to make sure the new MSI ends up in the Drop folder alongside its compadres. Surprisingly, this is actually a tiny bit more complicated than creating the MSI itself, but not too onerous.

Scroll down nearly to the bottom of our new BuildProcessTemplate to the container labeled "Copy Files to Drop Location". Here we need to add a sequence after "Copy to Drop". We're going to modify it to look like the following when it's finished.

First, we add a sequence activity after the Copy to Drop location. Then we'll need a variable scoped within our new sequence (this will hold the results of a find file operation that matches any MSI files we may have just created). This variable should be an IEnumerable.

Now, drag a FindMatchingFiles activity into the sequence activity and set its Result property to the variable we just created and its MatchPattern property like this:



Now that we've gotten a list of the MSI files in the Sources directory, add a ForEach activity after FindMatchingFiles. Its Value property should be the variable holding the names o the MSI files.

Finally, we drag an InvokeProcess activity into the ForEach and configure its FileName property with "xcopy.exe" and specify Arguments with

String.Format("""{0}"" ""{1}""", item, BuildDetail.DropLocation

Away we go!

So...one day last month the ever-erudite Jon Brammeier (software developer extraordinaire) said to me, "You should start blogging about some of the things you go through to make things work around here. Think about what a hassle it was for you to find all the info about [this, that, other thing]. Don't you think other people would like to know that you actually found a reasonable solution?"

Well, yeah. OK. Can't argue with that!

So, away we go...up, up and away into the blogosphere. Stay tuned and I'll let you know all about the interesting, obscure, nifty, and/or nerdy things I tend to pick up as I go.

I welcome your comments, critiques, and feedback. If exposing my foibles and travails helps you in your own endeavours, I hope you'll take a moment to let me know. And if you think my ideas could be refined (get your own blog) I really hope I can benefit from your observations and experience as well!

Cheers for now,
Kerry Cakebread