AdventureWorks 2000 tinyint nightmare

You’re going to love this one!

Some of our integrations tests use AdventureWorks 2000. One developer’s test updated a Sales.SalesOrderDetail. The test suddenly blew up with a SQL Server 2005 error that complained about exceeding the size limit of a TinyInt.

The error didn’t say where this TinyInt lived or what the column was called. The best we could tell was that the exception occurred while updating Sales.SalesOrderDetail.

SalesOrderDetail has a TinyInt column, “LineNumber”, which was not jeopardized by the update.

Aside: I have a different AdventureWorks db on my machine that doesn’t have “LineNumber” or a TinyInt but it does have, “OrderQty”, which is a SmallInt. I would have suspected that (“SmallInt, TinyInt, what’s the difference?”); there would be no problem there either.

No other developer could reproduce the SQL exception.

After hours of hair-pulling, the developer found that there is an update trigger on SalesOrderDetail that updates the parent Sales.SalesOrderHeader. SalesOrderHeader has an update trigger that increments SalesOrderHeader.RevisionNumber … which is a TinyInt.

Over the course of many tests, that RevisionNumber kept creeping up … until it hit 255. Each developer has his own AdventureWorks copy so no surprise no one else could reproduce it.

You can blame the victim if you wish; should have had adequate rollback logic all along.

But I have to admire the genius who decided that you’d never exceed 255 updates of a SalesOrderHeader in a test database.

Let’s thank SQL Server for throwing an exception on exceeding the max for a TinyInt without telling us the name of the column or table involved.

Kudos also to the other evil genius in this mess who wrote a trigger on one table that fires a trigger on another table to update the RevisionNumber … and didn’t bother to check the type or implement exception logic that would help clarify the problem.

There must be an award for this kind of thing.

Lesson #1: If you are using AdventureWorks 2000, be sure to reset all SalesOrderHeader.RevisionNumber values to 0. It’s too late to mess with the schema.

Lesson #2: Hey, SQL Server folks, how about a little more help in exception messages?

Lesson #3: Please write good exception messages yourself … messages that would give the poor sod who received it a fighting chance of understanding the problem. Extra credit if you suggest how to fix it.

Lesson #4: Business logic in the database is killing us out here. You DBAs have got to be more helpful.

Lesson #5: Stop trying to squeeze every last byte out of the schema. TinyInt should be used only in the most extreme circumstances and never where it could be incremented.