Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #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!

Reply With Quote
  #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

Reply With Quote
  #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!

Reply With Quote
  #4 (permalink)  
Old December 13th, 2007, 08: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.
Reply With Quote
  #5 (permalink)  
Old December 13th, 2007, 09: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
========================
Reply With Quote
  #6 (permalink)  
Old December 14th, 2007, 06: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".


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

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

--Jeff Moden
Reply With Quote
  #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
Reply With Quote
  #10 (permalink)  
Old March 28th, 2008, 07:24 PM
Friend of Wrox
Points: 1,533, Level: 15
Points: 1,533, Level: 15 Points: 1,533, Level: 15 Points: 1,533, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 474
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 10:53 AM
Alter Column - Default Value sasidhar79 SQL Server 2000 2 January 18th, 2005 06:30 AM
Alter Default on a Column Gert SQL Server 2000 4 November 28th, 2003 07:58 AM
Alter Table - Add Column liz@trinityholdings.co.za Access 2 July 29th, 2003 03:06 AM



All times are GMT -4. The time now is 02:46 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.