Strange gotcha with column names

Here’s a funny thing I discovered while working with a table I created by importing data from Excel. (I know, I only have myself to blame.) A number of the column names ended up with a trailing space on the end. They were Y/N type columns where the value was either Y or null. I decided to update all the nulls to Ns so the nulls wouldn’t trip me up later. So I typed a query like this: update

set = isnull(, ‘N’). I noticed Intellisense had underlined the column name but I knew what I was doing and assumed it was still refreshing its cache. The update ran fine but when I tried to select that column I got a ‘no such column’ error. WTF?

I can only assume this is a side-effect of the way you can update things like rowset functions and not just tables. I’ve done a bit of playing around and found that (surprise surprise) some variants will work and some will fail. Try it for yourself:

create table dbo.TestTable
 [TestColumn ] varchar(20) null

--This update will give the result: (0 row(s) affected)
update dbo.TestTable 
set TestColumn = 'hello';

--But this update will fail:
--Msg 207, Level 16, State 1, Line 2
--Invalid column name 'TestColumn2'.
update dbo.TestTable 
set TestColumn2 = 'hello';

drop table dbo.TestTable;

I suppose the moral of the story is: concentrate and check that updates affect the number of rows you expect. (Damn, that’s not going to work!)


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s