Invalid column name and ALTER TABLE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Invalid column name and ALTER TABLE

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-17 : 15:20:44

Hi,
I have a simple SQL query run in SQL 2000 QA, the last query assignment SELECT gives me the error like "Server: Msg 207, Level 16, State 3, Line 38
Invalid column name 'rowID'."

I just wonder the other queries work OK but the last one.
Please give me any advice to get rid of the problem.
Thank you in advance.


create table persons (id int, name varchar(50))
create table person_pets (personID int, petname varchar(50))


declare @previousID int, @petNames varchar(1000)

alter table person_pets add rowID int identity(1,1)
alter table person_pets add petnames varchar(1000) null

-- check to see an empty table --> this query works OK
select * from person_pets

-- sort table physically 
create clustered index ix_person_pets on person_pets(rowID)

insert persons
select 23, 'Oluf'
union all select 24, 'Christian'
union all select 25, 'Jenny'

insert person_pets(personID, petname)
values( 23, 'Fido')

insert person_pets(personID, petname)
values( 25, 'Shadow')

insert person_pets(personID, petname)
values( 23, 'Garfield')

insert person_pets(personID, petname)
values( 23, 'Casper')

-- check to see inserted data --> this query works OK
select * from person_pets


-- this query throws the SQL error "Invalid column name 'rowID'"

select @previousID = personID, @petNames = ''
from person_pets
where rowID=1


-- drop
drop table person_pets
drop table persons

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-17 : 15:27:55

Your script does not produce any errors for me when I run it in SQL Server 2005, but I get the same error as you in 2000. Try this instead:


create table persons (id int, name varchar(50))
create table person_pets (personID int, petname varchar(50))

alter table person_pets add rowID int identity(1,1)
alter table person_pets add petnames varchar(1000) null

go

declare @previousID int, @petNames varchar(1000)

-- check to see an empty table --> this query works OK
select * from person_pets

-- sort table physically 
create clustered index ix_person_pets on person_pets(rowID)

insert persons
select 23, 'Oluf'
union all select 24, 'Christian'
union all select 25, 'Jenny'

insert person_pets(personID, petname)
values( 23, 'Fido')

insert person_pets(personID, petname)
values( 25, 'Shadow')

insert person_pets(personID, petname)
values( 23, 'Garfield')

insert person_pets(personID, petname)
values( 23, 'Casper')

-- check to see inserted data --> this query works OK
select * from person_pets


-- this query throws the SQL error "Invalid column name 'rowID'"

select @previousID = personID, @petNames = ''
from person_pets
where rowID=1


-- drop
drop table person_pets
drop table persons

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-17 : 15:34:11

quote:

Originally posted by tkizer

Your script does not produce any errors for me when I run it in SQL Server 2005, but I get the same error as you in 2000. Try this instead:


create table persons (id int, name varchar(50))
create table person_pets (personID int, petname varchar(50))

alter table person_pets add rowID int identity(1,1)
alter table person_pets add petnames varchar(1000) null

go

declare @previousID int, @petNames varchar(1000)

-- check to see an empty table --> this query works OK
select * from person_pets

-- sort table physically 
create clustered index ix_person_pets on person_pets(rowID)

insert persons
select 23, 'Oluf'
union all select 24, 'Christian'
union all select 25, 'Jenny'

insert person_pets(personID, petname)
values( 23, 'Fido')

insert person_pets(personID, petname)
values( 25, 'Shadow')

insert person_pets(personID, petname)
values( 23, 'Garfield')

insert person_pets(personID, petname)
values( 23, 'Casper')

-- check to see inserted data --> this query works OK
select * from person_pets


-- this query throws the SQL error "Invalid column name 'rowID'"

select @previousID = personID, @petNames = ''
from person_pets
where rowID=1


-- drop
drop table person_pets
drop table persons

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thank you so much for your help.

BTW, why can the GO statement solve the problem? Can you explain it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-17 : 15:37:39

I can't explain it. It should work without it, perhaps that's why they "fixed" it in SQL Server 2005.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources