Monthly Archives: December 2011

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
);
go

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

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

drop table dbo.TestTable;
go

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!)

Advertisements