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

May 30th, 2005, 11:15 PM
|
Registered User
|
|
Join Date: May 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!
|

May 31st, 2005, 01:27 AM
|
Friend of Wrox
|
|
Join Date: Apr 2005
Posts: 186
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi,
use GO in between
Prashant
|

May 31st, 2005, 07:47 AM
|
Registered User
|
|
Join Date: May 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi Prashant, thanks the speedy response!
|

December 13th, 2007, 09:56 AM
|
Registered User
|
|
Join Date: Dec 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by Prashant.k.m
Hi,
use GO in between
Prashant
|
Thanks Prashant, it worked for me...
Dhaval.
|

December 13th, 2007, 10:31 AM
|
 |
Wrox Author
|
|
Join Date: Dec 2004
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
|
|
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
========================
|

December 14th, 2007, 07:03 PM
|
Friend of Wrox
|
|
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
The semicolon acts as "GO".
|

March 25th, 2008, 03:18 PM
|
Registered User
|
|
Join Date: Mar 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

March 25th, 2008, 07:08 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
What service pack are you using and which edition are you using?
--Jeff Moden
|

March 28th, 2008, 07:09 PM
|
Registered User
|
|
Join Date: Mar 2008
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

March 28th, 2008, 07:24 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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
|
|
 |