Technical Debt

A WTF Moment!

Technical Debt: a neologistic metaphor referring to the eventual consequences of poor or evolving software architecture and software development within a codebase.

An example? (Actually I'm not sure if this qualifies or is more of a "misguided genius" thing):

I need to make up two mutually exclusive lists for markets to support an existing provider and a new provider of the same Ancillary product.

Problems with QUOTENAME

So, lets get one thing out of the way before we begin.... Dynamic SQL is generally a bad plan. That being said there are a few scenarios where it is genuinely the best choice. For example in catch all type queries commonly encountered on web forms. (as this excellent post by Gail Shaw illustrates).

A major concern whenever you use dynamic SQL is SQL Injection. To secure yourself from this you have to validate your inputs for any DML code - (If you use parameterised dynamic sql with sp_executeSql then you are secure from a pure parameter standpoint)

sqlcmd.exe unspecified error

This bit me in the ass today.... I have a continous integration system that runs sqlcmd.exe to push scripts to a target database. Occasionally my build would fail for no apparent reason. The logs contained a message like this:

Sqlcmd: Error: Internal error at ExecuteSqlCmd (Reason: Unspecified error)

if you search online for that error then you get directed pretty quickly to this article which effectively says:

In short, this bug is fixed in builds above 10.00.4266 for SQL Server 2008 SP2.

Which seems pretty straightforward. And it is.....**IF** you realize that what's actually important is the version of sqlcmd.exe running LOCALLY to the build machine, not the Hotfix version applied to the database server....... if you see this kind of problem then you can check by running sqlcmd.exe /? and checking the version header. On my build server (running team city) the version was:

Version 10.0.1600.22 NT x64

which *is* effected by the bug. Make sure you run that on the machine that is actually calling sqlcmd, not the database However the version on the database server (SELECT @@VERSION) was:

Microsoft SQL Server 2008 (SP3) - 10.0.5766.0 (X64) Oct 15 2011 15:50:50 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

And because I looked at that first, then I wasted a bunch of time.... Sigh...... CI....