Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 30th, 2005, 11:15 PM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default alter add puzzle: Invalid column name

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!

  #2 (permalink)  
Old May 31st, 2005, 01:27 AM
Friend of Wrox
 
Join Date: Apr 2005
Location: Cochin, , India.
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

use GO in between

Prashant

  #3 (permalink)  
Old May 31st, 2005, 07:47 AM
Registered User
 
Join Date: May 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Prashant, thanks the speedy response!

  #4 (permalink)  
Old December 13th, 2007, 09:56 AM
Registered User
 
Join Date: Dec 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Prashant.k.m
 Hi,

use GO in between

Prashant

Thanks Prashant, it worked for me...

Dhaval.
  #5 (permalink)  
Old December 13th, 2007, 10:31 AM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
========================
  #6 (permalink)  
Old December 14th, 2007, 07:03 PM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The semicolon acts as "GO".


  #7 (permalink)  
Old March 25th, 2008, 03:18 PM
Registered User
 
Join Date: Mar 2008
Location: CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #8 (permalink)  
Old March 25th, 2008, 07:08 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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

--Jeff Moden
  #9 (permalink)  
Old March 28th, 2008, 07:09 PM
Registered User
 
Join Date: Mar 2008
Location: CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #10 (permalink)  
Old March 28th, 2008, 07:24 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
What do I need to add/alter? obrienkev C# 2005 1 November 1st, 2007 12:04 PM
Alter Column SQLScott SQL Server 2005 5 January 5th, 2007 11:53 AM
Alter Column - Default Value sasidhar79 SQL Server 2000 2 January 18th, 2005 07:30 AM
Alter Default on a Column Gert SQL Server 2000 4 November 28th, 2003 08:58 AM
Alter Table - Add Column liz@trinityholdings.co.za Access 2 July 29th, 2003 03:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.