My Favorite Tools

These are some of my favorite tools and services.

Password Safe - I've tried other password managers. I like this one the best.

Freeplane - A really nice free mind mapper.

Remote Desktop Connection Manager - If you use remote desktops in Windows, you simply must have this utility.

XML Marker - Nice lightweight XML editor.  Let's you easily copy to a grid (i.e. Excel).

Agent Ransack - search through your Windows folders.  Really quick, flexible without being overly complicated.

Console2 - a more advanced form of the Windows command prompt - also gives you a great PowerShell console.

WinMerge - an easy to use diff and merge tool.

soapUI - excellent tool for testing web services.

EDI Notepad - Need to edit or view EDI messages?  Here ya go.

LINQPad - for learning and experimenting with LINQ (and more).

T-SQL UPSERT Atrocity

How to not do an "upsert" from one table to another.  Yes, this example came from a real stored procedure.  Yes, this stored procedure eventually caused problems.  Some of the table and field names have been changed for this example.

ALTER PROCEDURE [dbo].[UpdateDestinationTable]AS
    BEGIN
        DECLARE @counter INT
        DECLARE @recordCount INT
        SET @counter = 1

      -- How many records are in the source table?
        SELECT  @recordCount = COUNT(*)
        FROM    SOURCE_TABLE

      -- Let's loop that many times!
        WHILE @counter <= @recordCount
            BEGIN
                DECLARE @pur_WBSnum VARCHAR(15)
                DECLARE @pur_matnum VARCHAR(40)
                DECLARE @pur_receiptdt DATETIME
                DECLARE @pur_receiptid VARCHAR(15)
                DECLARE @pur_vendorid VARCHAR(10)
                DECLARE @pur_qtyrecvd DECIMAL(10, 3)
                DECLARE @pur_sloc VARCHAR(4)
                DECLARE @pur_plant VARCHAR(4)

       -- Populate our variables with values from a single record
                SELECT  @pur_WBSnum = pur_WBSnum,
                        @pur_matnum = pur_matnum,
                        @pur_receiptdt = pur_receiptdt,
                        @pur_receiptid = pur_receiptid,
                        @pur_vendorid = pur_vendorid,
                        @pur_qtyrecvd = pur_qtyrecvd,
                        @pur_sloc = pur_sloc,
                        @pur_plant = pur_plant
                FROM    SOURCETABLE

        -- pur_id is an identity column in the source table.
        -- There will be a record with a pur_id value that
        -- matches the iteration of the loop we are on... right?
                WHERE   pur_id = @counter

        -- Update the corresponding record in the destination table
                UPDATE  DESTINATION_TABLE
                SET     VENDOR_ID = @pur_vendorid,
                        QTY_RECVD = @pur_qtyrecvd,
                        SLOC = @pur_sloc,
                        PLANT = @pur_plant
                WHERE   PROJECT_ID = @pur_WBSnum
                        AND DAX_ITEMID = @pur_matnum
                        AND RECEIPT_DT = @pur_receiptdt
                        AND RECEIPT_ID = @pur_receiptid

        -- Nothing was updated?
                IF @@ROWCOUNT = 0

        -- Then we must need to do an insert instead!
                    INSERT  INTO DESTINATION_TABLE
                            (
                              PROJECT_ID,
                              DAX_ITEMID,
                              RECEIPT_DT,
                              RECEIPT_ID,
                              PO_ID,
                              VENDOR_ID,
                              QTY_RECVD,
                              INVENT_TRANS_ID,
                              DATAAREA_ID,
                              SLOC,
                              PLANT
                            )
                    VALUES  (
                              @pur_WBSnum,
                              @pur_matnum,
                              @pur_receiptdt,
                              @pur_receiptid,
                              ' ',
                              @pur_vendorid,
                              @pur_qtyrecvd,
                              ' ',
                              ' ',
                              @pur_sloc,
                              @pur_plant
                            )

                -- Re-initialize our variables and increment the counter

                SET @pur_WBSnum = NULL
                SET @pur_matnum = NULL
                SET @pur_receiptdt = ' '
                SET @pur_receiptid = NULL
                SET @pur_vendorid = NULL
                SET @pur_qtyrecvd = 0
                SET @pur_sloc = NULL
                SET @pur_plant = NULL
                SET @counter = @counter + 1

            END
        -- We can count on TRUCATE to restart the
        -- identity column seed at 1, right?
        TRUNCATE TABLE SOURCE_TABLE

    END

BizTalk EDI and diacritical marks

Minor BizTalk tip: If you're outputting X12 EDI and you're getting funky characters instead of the proper accent marks (diacritical marks), set the CharacterSet property in the EDI pipeline to "Extended" (without the quotes).

iTunes Preorder - just say "no"

When you preorder something, don't you expect to be able to get it as soon as possible? At least as soon as the general public that didn't preorder it, if not a little sooner?

Apparently not when you preorder something on iTunes.

I preordered the Bruce Springsteen album Magic, which was released today. But as of 10:36 PM Eastern, I am still unable to download it. Clicking on the preorder under my account tells me it is not currently available in the US.



Which is kind of funny, because it is certainly available for purchase from the iTunes store. It must just be unavailable to the people stupid enough to preorder it!

The next time I want an album on its release date, I'll just go to a real store and buy the CD. At least I'll get the little booklet with it.

There's absolutely no way I'll ever preorder anything on iTunes again.

Looks like I'm not the only one who has had a bad experience with iTunes preorders.

Dynamics AX and Version Control

Microsoft Dynamics AX 4.0 has version control capabilities. If you are used to using version control in Visual Studio, you may find some aspects version control in AX to be... let's say "odd."

I don't really want to bash the AX development team because they have done some amazing and wonderful things with AX. The object layering is great. So is the built-in data dictionary with its extended data types and ability to synchronize the schema of the underlying database system. So is the "best practices" checking and unit testing tools.

It is just bewildering that they have been able to implement such advanced features as those and then fudge something as old and established as version control, especially since version control has already been implemented by Microsoft in Visual Studio.

So my first blog about version control in AX is going to be a review of the parts of it that are "different" than what you may be expecting if you are coming from a different environment.

Visual Source Safe
For starters, they tied version control explicitly to Visual Source Safe 2005 and "Source Depot", one of the version control systems Microsoft uses internally. Yes, you can use other source control systems - if you are insane enough to write your own version control class. Considering the built-in functionality is a bit flaky, I'm not going to play Russian Roulette and hope I implemented my own classes correctly.

Come on, AX team! Visual Studio has the ability to work with practically any source control system on the market out of the box. You couldn't consult with them to figure out how to do it? Maybe even crib some code from them, even if you had to rewrite it in X++?

The Team Server
No, that's not "team server" as in Team Foundation Server. That would make too much sense. This refers to a special server Microsoft wants you to set up in order to use Version Control with AX. The purpose? To dispense unique object IDs when developers create new objects. That way different developers won't create multiple objects with the same IDs.

Holy kluge, Batman! This little problem has been solved quite nicely across the rest of the known universe by using GUIDs (AKA UUIDs). Bingo. Now every new object gets a unique ID without the need for some silly ID server. Did they go the "ID server" route because they didn't feel like assigning new GUIDs to existing objects as part of the upgrade process? Because they didn't want to change the data type of object IDs? Bad trade-off, IMHO.

Revision Labeling
Sometimes known as tagging, this feature of source control systems allows you to apply a label to a specific revision of an object (file). This lets you to identify milestones in the code history, identify files that were modified for a particular change, etc. There appears to be no support for this in the Version Control functionality of AX.

This is a shame, since it would have fit in really well with AX projects. You could select all the objects in your project and label them with a work order number, making it very easy to identify which objects should be retrieved from source control to build and deploy the changes.

Instead, you have to switch to the VSS client, find your objects all over again, and label them that way. What a missed opportunity. Maybe they thought the term "label" would be confused with the AX labels used to store string constants?

If they wanted to go way above and beyond what everyone else is doing, they would have included the ability to do branching and merging of AX projects right from the IDE.

Too Many Stinking Messages
Oh boy, does AX like to give you a lot of feedback. The act of checking out an object results in:

1. A dialog box asking "Do you want to reload XYZ?" Which of course really means "Do you want to replace your local copy of XYZ with what is in Version Control?" But they couldn't ask that.



2. An Infolog showing you that AX did indeed update and check out the object. These messages are labeled "just for your information and do not require you to take any action." Oh. Thanks. Can I turn this off?



3. Just for good measure, a separate "Message window" also listing the object you just checked out. Oddly enough, there is no explanation at all. Just the name of the object. I'd like to turn this off too.

Confusing Prompts and Terminology
Operation: Check out
AX Language: "Do you want to reload XYZ?"
Translation: "Do you want to replace your local copy of XYZ with what is in Version Control?"

Operation: Undo check out
AX Language: "Do you want to reload XYZ?"
Translation: "Do you want to revert XYZ to the way it was before you checked it out?"

Operation: Adding an object to Version Control
AX Language: Right click on the object and pick "Create"
Translation: Right click on the object and pick "Add to Version Control"

Halfway Implemented Checked Out Indicators
AX does give you the option of color coding objects that you have checked out (they turn blue). However, it doesn't color code objects that other people have checked out. It would be nice if objects would turn red or have a padlock next to them if they were checked out by other people.
Am I being too hard on the AX team? Possibly. But then again I'm expecting great things from AX!

In my future postings, I hope to cover how VSS is working behind the scenes and what particular problems I've run into using it and setting up daily builds.

Features Business Application Platforms Should Have

There are a lot of software packages that go way beyond being mere applications and become development platforms in their own right. WebSphere Portal, SharePoint, Lotus Notes, and Dynamics AX come to mind.

These "apps" allow you to drastically customize the way they work and add your own functionality. In fact, they probably won't be very useful for you unless you do treat them as a platform rather than a finished app.

Unfortunately, in many cases these business application platforms don't follow through when it comes to rounding out their platform features. Here's a short list of just some of the things that seem to slip vendors minds for at least the first dozen releases.

Application Modes
There are times when you want to take the application down for maintenance or upgrades, and you don't want users to just get a "server not available" message. You should be able to put your application into "Offline" mode. When end users try to access the system they should see a nice message like "The system is currently not available" or a custom message provided by the administrator. Obviously this mode can't be dependent at all on the database and only trivially dependent on the application server.

There are other times when you need to get into the application to make configuration changes or troubleshoot a serious problem, and you don't want end users mucking around with the data or doing real work they may lose. So all applications need an "Admin Only" mode. People with administrator rights should be able to get into the system, but a "not available" message should be displayed to all other users. There should be an option to let end users in with "read only" access.

Deployment and Change Management
It still never ceases to amaze me how, in this Sarbanes-Oxley world, source control and deploying customizations from development to test to production is still an afterthought for many software vendors.

The next time that sales guy is showing you how easily the application can be customized, ask him "where's the deploy customizations button?" and watch the blank stares. If he tells you the changes need to be manually repeated in production, show him the door. If he shows you some convoluted export and import procedure ask for a demonstration that takes 15 minutes or less with zero manual editing of the export file.

Also make sure he shows you how your development environment can be hooked into your source control system. Have him demonstrate labeling (tagging), branching, concurrent checkouts, and merging. If you get more blank stares, show him the door.

Data Refresh
This one would be a huge bonus. You normally want your test, training, and development environments to have data that is fairly fresh. This is rarely a matter of just doing a backup + restore of the production database, since the non-production environments probably have configuration information in the database that is different from the production environment. So having a "data refresh" utility that knows which data not to refresh would be wonderful.

Yes, you can use tools like Red-Gate Data Compare. But that requires you to dig through the hundreds, or maybe thousands of poorly documented tables the vendor has created.

Any vendors that really want to work magic should incorporate data obfuscation so the non-production environments contain data that is "real" but has fictitious values for things like customer names, bank account numbers, phone numbers, etc.

Freaky Nothingness

Lately I've been trying to figure out exactly how the heck Visual Basic.NET handles Nothingness.

According to the Visual Basic Language Reference, the Nothing keyword "represents the default value of any datatype". For an integer, setting it to Nothing would make its value zero. For an object, setting it to Nothing makes it an object with no reference to an instance. For a string, because strings are objects in VB.NET, setting it to Nothing would make it an object with no reference to an instance.

This is important, because I originally misunderstood the purpose and use of the Nothing keyword. I thought it was the VB.NET variable equivellant of NULL, which it decidedly is not.

Based on its name I expected setting an integer to Nothing would set it to... well, nothing instead of zero. Maybe DefaultValue would have been a better name for this keyword. Setting reference-type variables to Nothing works fine, but setting value-type variables (like integers) to Nothing doesn't buy me anything.

So how can I set VB.NET value-type variables to something like NULL?

Tony Patton's article Working with null values in .NET indicates you can set "any type of data" to DBNull, but my experiments result in Visual Studio.NET 2003 telling me it can't convert System.DBNull.Value to an integer.

The blog posting The Truth about Nullable Types and VB... provides some good information:

There is no way in the current CLR to look at an Integer variable and determine whether it has never been assigned a value - the fact that it contains zero doesn't necessarily mean that it hasn't been assigned a value.

Crap. It also says that VS.NET 2005 will be adding a generic type called Nullable(Of T) that allows you to make nullable versions of any value type. That doesn't help me much now.

I guess I'll have to revert to magic numbers to indicate that my value-type variables do not contain values.