The puzzle
  if I attempt to execute these 3 lines together (alter & select & update)
    in SQL Query Analyzer (version 8.00.194)
  then
    I would get the error message of Invalid column name
    WITH
    the column NOT added.
  "jnk" table happens to be empty but same puzzle with one row.

The 3 lines:

  alter table jnk ADD WHYBAD8 numeric;
  select * from jnk;
  update jnk set WHYBAD8 = 3;

The output & error message:
  /*-----------------------------
  alter table jnk ADD WHYBAD8 numeric;
  select * from jnk;
  update jnk set WHYBAD8 = 3;
  -----------------------------*/
  Server: Msg 207, Level 16, State 1, Line 1
  Invalid column name 'WHYBAD8'.

HOWEVER, if I execute ALTER & SELECT together only,
then execute UPDATE, then all works as expected!

BTW, the same bug persists with char or varchar instead of numeric.

Please advise!

Hi,

use GO in between

Prashant

Hi Prashant, thanks the speedy response!

Thanks Prashant, it worked for me...

Dhaval.

Just an FYI...i don't get that error in 2005. For example:

create table jnk
(
  Firstname varchar(20)
)
insert into jnk (firstname) values ('scott')

alter table jnk ADD WHYBAD8 numeric;
select * from jnk;
update jnk set WHYBAD8 = 3;

select * from jnk;

drop table jnk

========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

The semicolon acts as "GO".

Hi there,
I had the same problem as JL1024 and getting the same "Invalid column name" error message with a very simple script.
Here it is (in a "general" format):

ALTER TABLE table1
ADD column2 real;

UPDATE table1
SET column2 = column1; --- column1 is real too

SQL fails to execute the UPDATE statement right after the
ALTER TABLE statement. Does anybody know why this is happening?

Adding a "GO" in between the two queries solves the problem
as Prashant suggested but this shouldn't be necessary (see comment
by Peso).

P.S.
I'm using SQL Server 2005

Adalberto Pineda

What service pack are you using and which edition are you using?

--Jeff Moden

Jeff,
Here's the version I'm using:

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2
(Build 3790: Service Pack 1)

Not using 'GO' doesn't seem to be a syntax error.
So why is it that SQL is not executing the UPDATE
statement?


Adalberto Pineda

That could be the problem... looks like you're using the 64 bit version... I heard about all sorts of problems with it. Here's what I'm using...

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

--Jeff Moden