SQL Server 2005 No Corresponding BEGIN TRANSACTION

This one turned out to be yet another highly misleading error message. It also only depleted me of about 24 mins of life-force, so therefore does not strictly qualify as a Journey Of Pain, but what the heck, with my insatiable public constantly demanding more material sometimes quality is compromised.

In the beginning was a tiny SQL Script no more than 10 lines or so; and this script had one purpose in life and that was to add a new NOT NULL column to an existing table: I will call it BRUCE, tblBruce to be precise. So here is the script:

BEGIN TRAN
BEGIN TRY
ALTER TABLE tblBruce ADD IsMandatory BIT NOT NULL
END TRY

BEGIN CATCH
ROLLBACK;
END CATCH

COMMIT;

..and that friends will return you the highly misleading error message “No Corresponding BEGIN TRANSACTION”.

It lies…while not making me quite as angry as the spurious reasons advanced for the Australian Intervention in East Timor in 1999, does tee me off a bit. I’d rate it a ‘Laksa stain on my tie while eating lunch’ – grade tee-off.

The real error is that I have not specified a DEFAULT VALUE for the new column. Because the column is new no values exist for the existing rows, and because we have specified NOT NULL we have to provide a DEFAULT VALUE for all those existing rows, like so:

BEGIN TRAN
— Add new column to Bruce with default value
BEGIN TRY
ALTER TABLE tblBruce ADD IsMandatory BIT NOT NULL DEFAULT 0
END TRY
BEGIN CATCH
ROLLBACK;
END CATCH
COMMIT;

So when adding a new NOT NULL column to an existing table, provide a default value for the existing rows and you will be spared 24 mins of Laksa-stain grade ennui.

You know it makes sense.

Advertisements

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: