Tomorrow I start grad school. I'm attending Robert Morris and working on an MBA. I know it's not a prestigious school, but my reasons aren't to get a high-profile degree. I'm more interested in the experience, and RMU is convenient and inexpensive (relatively speaking). It is amusing that they named a school after a famous hacker, though... :)

All this talk about business schools reminds me of a joke I heard, back in the day:

There was a Wharton grad taking care of business at the urinal. A Penn Stater walked in to the urinal next to him, and did his thing. When they met at the sink, the Penn Stater looked in the mirror, fixed his hair, and proceeded to walk out without using the sink. The Wharton grad, in a snobby voice said, "At the University of Pennsylvania, we learned proper manners and to wash our hands after we relieve ourselves."

To which the Penn Stater replied, "At Penn State we learned not to pee on our hands."

...when a Wharton grad starts talking about the price of oil being "negotiated". Um, Mr. Trump, did you ever hear of the laws of supply and demand? Oil is a commodity - the price is set by the market. There ain't no getting around that - it is globally traded. You'd think a capitalist like yourself would know better. The problem isn't supply, per se, but demand, as the rapidly growing Asian economies (India and China) are consuming more and more than they did before (and it should be noted that on a per capita consumption basis, they still aren't even in the same ballpark as we are, so things are only going to get worse).

I agree with The Donald - we need to look harder at alternative energy sources (and I'm not expert on anything science related, so I won't go into any details about that, because I'd probably just be wrong). But to suggest that we can just haggle with the sheiks over the price of oil? Come on now, Mr. Trump - you know better than that.

So, the meeting I referred to here has been postponed. Sorry to see it, but on the other hand, glad, because I couldn't attend and I really wanted to see the presentations. See Rich's post for more details.

I'm not a fan of Microsoft Project. Every time I try to figure out how to use it, I end up pushing the balance button and blowing up everything. But then I realized I was using it wrong - its not really a good tool to track software development - you end up doing a bunch of artificial things to make it all look happy, but what does it really tell you?

This method looks really promising. As any of the readers of my blog may already realize, I'm trying to become one of those there agile developers. And with my education in Quantitative Business Analysis (or what most schools that aren't trying to be overly pretentious call Management Science), the nice line graph really speaks to me. With this style of charting, you can really see how things are progressing, and it's real easy to get to this kind of chart from the Excel project planning style Joel recommends. Kind of ironic seeing Joel and Agile in the same article, espousing the same viewpoint, no? :)

OK, so I should have put this up sooner, but things have been busy. Trying to get the house straightened up after they finally finished our addition, get the decks cleared for starting the MBA program, and get some other things out of the way, and before you know it, time passes... :)

For the August BADNUG meeting, Rich will be having two presentations, one on ExcelXmlWriter, and one of Skinning DotNetNuke. Check out his blog for details.

You need to uninstall the current version of SQL 2005 you're using for whatever reason (installing the next beta/CTP, for example). You take a few minutes in the morning, and uninstall while you're reading your mail and updating the bug database. You realize you need to actually use SQL Enterprise Manager to do some work, so you launch it. It gives you an error about missing SQLDMO. You could go ahead and install the next edition - that will fix the problem, but you just don't have time right now. How do you fix it so you can get some work done?

This will take care of it for you:

regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll"

The problem is, install puts in a new version of this file, and registers it. The uninstall process removes the new file, but doesn't re-register the old one. Ah, DLL Hell, we missed you so!

 

I discovered this flag when I was trying to build a query to get dependencies, when I was hunting down where a table was being changed. The sysdepends table has a column - resultobj - that contains 1 if the specified procedure updates (or inserts) a table. It also has a flag - selall - that will tell you if a table is have a SELECT * run against it, which is useful to know so you can correct it, but we'll get into that later.

Here is a query to find all of the stored procedures (or triggers) that make changes to a table's contents:

SELECT DISTINCT
    po.name
FROM    sysdepends d
    INNER JOIN sysobjects po
        ON d.id = po.id
    INNER JOIN sysobjects do
        ON d.depid = do.id
WHERE    do.name = 'TABLE NAME GOES HERE'
AND    d.resultobj = 1

Consistency is one of the more important aspects of what we do, and the one we trip over most often. Consistency is the reason for code and design standards. If things are not done consistently, it becomes difficult for programmers that come after us (or ourselves, 2 weeks later) to figure out what the code is supposed to do.

One of the easiest things to be consistent about in your database is to have a name mean one, and only one, thing. If you give a column the name 'name', then it better mean the same thing as every other 'name' column in the database. So, if you define a 'name' column as VARCHAR(200), then every 'name' column should be VARCHAR(200). It makes it easier for somebody maintaining the code to know how much space should be reserved for a name, and keep the code consistent.

Here is a SQL snippet that will show you any columns that have the same name, but don't have consistent characteristics. Note that this code runs somewhat slowly - it is possible to get the results faster if you access the system tables directly. However, it is good practice to use the INFORMATION_SCHEMA tables, since Microsoft has guaranteed us that they will remain usable from one version of SQL Server to the next. Also, in the case of "best practice" checking queries, performance isn't as important, and the INFORMATION_SCHEMA query isn't a terrible performer for one time use. Anyway, here's the code:

SELECT    c.TABLE_CATALOG,
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    c.DATA_TYPE,
    c.CHARACTER_MAXIMUM_LENGTH,
    c.NUMERIC_PRECISION,
    c.NUMERIC_SCALE
FROM    INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN INFORMATION_SCHEMA.TABLES t
        ON c.TABLE_CATALOG = t.TABLE_CATALOG
        AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
        AND c.TABLE_NAME = t.TABLE_NAME
WHERE    t.TABLE_TYPE = 'BASE TABLE'
AND    c.COLUMN_NAME IN (
    SELECT    c.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.COLUMNS c
        INNER JOIN INFORMATION_SCHEMA.TABLES t
            ON c.TABLE_CATALOG = t.TABLE_CATALOG
            AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
            AND c.TABLE_NAME = t.TABLE_NAME
    WHERE    t.TABLE_TYPE = 'BASE TABLE'
    GROUP BY
        c.COLUMN_NAME
    HAVING    COUNT(DISTINCT c.DATA_TYPE) > 1
    OR    COUNT(DISTINCT c.CHARACTER_MAXIMUM_LENGTH) > 1
    OR    COUNT(DISTINCT c.NUMERIC_PRECISION) > 1
    OR    COUNT(DISTINCT c.NUMERIC_SCALE) > 1)
ORDER BY
    c.COLUMN_NAME,
    c.TABLE_NAME

There are basically two schools of thought in relational database circles around what makes a good key. There are some who say that the key should be meaningful (and also usually don't mind compound keys). So, for example, they would consider using something like a employee's social security number (at least, in the pre-identity theft world - these days using an SSN is being more and more frowned upon). SSN is probably safe - it should never change (although it could be entered wrong). Using a telephone number as a primary key is guaranteed to cause nightmares - telephone numbers change all the time.

Because finding a never-changing meaningful value is so difficult, the other school of thought suggests using a meaningless value as the key. This means that the value won't change, because it is based on nothing other than a "random" key generation algorithm (more on that shortly). Of course, sometimes that key is seen by the users, and at that point has meaning, but it's meaning is directly related to its usage in the database, and will not change via the outside world, and so for our purposes can still be seen as meaningless. An example of this would be an order number. The customer may be given the order number, and staff members may use the order number to track and follow an order, but the order number is guaranteed never to change.

In SQL Server, there are two ways to easily obtain this "random" meaningless key - Identity columns, and Guid values. In general, if you are not dealing with a distributed database, identity columns will be preferable, since they are smaller and the generation algorithm is less complex. Identity values are garanteed to be unique for a local table. However, if you are using a distributed database, Guids are much better. It is impossible to guarantee that an identity column will hold a unique value across multiple databases and servers. However, because the generation algorithm for a Guid makes any Guid unique, they are perfectly suited to distributed operations.

So, this brings us to our next rule, which, as I pointed out, is controversial: Never use a meaningful value for a primary key. In terms of implementation, this means that the primary key should always be either an Identity column, or a Guid column. The following query will show you any tables in your database that do not comform to this rule:

SELECT    kcu.TABLE_CATALOG,
    kcu.TABLE_SCHEMA,
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG
        AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
        AND tc.TABLE_NAME = kcu.TABLE_NAME
        AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE    tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND    COLUMNPROPERTY(
        OBJECT_ID(kcu.TABLE_CATALOG + '.' +
            kcu.TABLE_SCHEMA + '.' +
            kcu.TABLE_NAME),
        kcu.COLUMN_NAME,
        'IsIdentity') = 0
AND    COLUMNPROPERTY(
        OBJECT_ID(kcu.TABLE_CATALOG + '.' +
            kcu.TABLE_SCHEMA + '.' +
            kcu.TABLE_NAME),
        kcu.COLUMN_NAME,
        'IsRowGuidCol') = 0

Here is some code I had the privilege of debugging:

        public Participant GetParticipant(int OrganizationID, int ParticipantID)
        {
            DataTable _dt;
            _dt = ParticipantDM.GetParticipant(ParticipantID,OrganizationID);
            Participant _participant = null;
            if (_dt.Rows.Count > 0)
                _participant = new Participant(_dt.Rows[0]);

            return _participant;
        }

There are lots of little tiny problems with this code that I won't bore you with - I'm sure you can see them. The big problem, though, is what happens when, because of a poorly written query, you end up getting multiple rows back from the database. You just use the first row, and hope it's right. Of course, the problem is, this masks the real bug - that there is a problem with the query. It is hard to debug this kind of problem, because the error report from QA will usually be a strange data error that really seems like an unexplained anomoly. Of course, we know that this is not the case - there is always a real error somewhere.

How do you avoid this problem? If you are writing data access code, and you are expecting zero or one rows, then the case of multiple rows should be treated as a failure. You can then pass the error back, either as an exception, or in the case of the accepted practice where I am currently working, by returning null. Regardless, it has to be clear to the outside world that something went wrong, instead of just showing bad data, leaving them scratching their heads.

        public Participant GetParticipant(int organizationId,
            int participantId)
        {
            DataTable dt = ParticipantDM.GetParticipant(participantId,
                organizationId);

            if (dt.Rows.Count == 1)
            {
                return new Participant(dt.Rows[0]);
            }

            return null;
        }

To go with my "series", I want to point out that there is a free tool from Microsoft, the SQL Server Best Practices Analyzer, that can help you with checking your database servers and databases for compliance with their recommendations. You can think of the tool as an FxCop for SQL. I should point out, it is not a new tool - it's been around for over a year now.

One of the practices they check for is the use of a primary key, as I mentioned previously. It also checks you databases to see if there are any things that will prevent them from being migrated to SQL Server 2005, which is good to know.

The tool is built on .Net, and it appears to be extensible, although I can't find any documentation on how to do it. However, poking around in its database and code, it seems that it can be figured out, so I'm planning to try my hand at creating a new "rule". I'll keep you posted.

Seems that there is now a "port" of DotNetNuke to C#: SharpNuke.Net. Seems kind of silly, since you can write your own modules in any .Net language you desire, but I guess somebody had to do it. After all, what would we talk about if we couldn't trash each other's language choices? :)