Mark Gilbert's Blog

Science and technology, served light and fluffy.

CEO: Chief Exec() Oddity

I have a side project that I’m working on building a desktop application based running on SQL Server Express, and ran into an interesting issue with T-SQL.

The Setup
Since I’m distributing this to users who are geographically distant from me, and because those users are firmly in the “poweruser” category – not programmers – I needed to provide an easy way for them to get their local copies of SQL Server Express updated when I modified the table structure.  I decided to build a bootstrapper class that runs through a series of SQL Scripts – each one with its own version number attached – until the local database was up to date.  The local database contains a table that stores the current version of the database (which is designed to match the application version), and it will only apply the scripts that need to be applied, and in the order that they need to be applied.

The Issue
The most recent database modification I needed to make was to add a new column to a database and then populate that column with the ID of that row.  It would have been handy in this case if SQL Server allowed me to define the Default value for a column as the ID column, but it specifically disallows that:

Msg 128, Level 15, State 1, Line 1
The name “id” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Ok, so Plan B.  Use an ALTER statement to add the new column, and then an UPDATE statement to populate it:

ALTER TABLE MyTable ADD MyNewCol [int] NOT NULL DEFAULT 0;
UPDATE MyTable SET MyNewCol=ID;

Simple, right?  Unfortunately, this pair throws an error:

Msg 207, Level 16, State 1, Line 0
Invalid column name ‘MyNewCol’.

Even when I tried to run this in SQL Server Management Studio (SSMS), it fails with this same error.  I think what was happening is that the ALTER statement didn’t completely commit to the database in time for the UPDATE statement to run.  If I ran these two separately in SSMS (highlight first statement, CTRL-E, highlight second statement, CTRL-E) it worked fine, but I wouldn’t have that option in script.  The statements would have to run back to back, and I would need to find a way to make them play well with each other.

The Solution
I did some digging on the interweb and came across this post: http://www.sqlservercentral.com/Forums/Topic255804-8-1.aspx#bm256576.  The solution was to wrap both statements in an EXEC() function call – basically treating them as dynamic SQL:

EXEC(‘ALTER TABLE MyTable ADD MyNewCol [int] NOT NULL DEFAULT 0’);
EXEC(‘UPDATE MyTable SET MyNewCol=ID’);

This worked.

Asking for Trouble
Then I got gutsy.  There isn’t anything after the UPDATE statement that depends on the value of MyNewCol – why would I need the EXEC call around that statement too?  This pair of statements should work just as well, right?

EXEC(‘ALTER TABLE MyTable ADD MyNewCol [int] NOT NULL DEFAULT 0’);
UPDATE MyTable SET MyNewCol=ID;

Yeah – gutsy really wasn’t working for me today.  As it turns out, this also fails with the “Invalid column name” error.  As a result, I went with the dual-EXEC() calls.

It’s puzzling why the UPDATE needed to be invoked via EXEC() too, and unfortunately I don’t have a good explanation for that.  At least I have a method that reliably adds and populates my column, so that will have to be good enough for now.

Advertisements

September 23, 2009 - Posted by | SQL Server

Sorry, the comment form is closed at this time.

%d bloggers like this: