Invalid column name and ALTER TABLE

Question

Post reply

Invalid column name and ALTER TABLE

johnsql-193053

SSCommitted

Points: 1657
More actions
June 17, 2008 at 1:27 pm

#70324

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
-- 1st query 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')
-- 2nd query: check to see inserted data --> this query works OK
select * from person_pets
-- 3rd query: 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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

When your procedure (or batch ) is being compiled there is no RowID in the table yet.

_____________
Code for TallyGenerator

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 2

Howerver, if I comment out the last query, and let the 2 preceeding queries run, I can see the rowID column in result DTSs. That is weird.

Any idea?

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 3

Sergiy (6/17/2008)
When your procedure (or batch ) is being compiled there is no RowID in the table yet.

Howerver, if I comment out the last query, and let the 2 preceeding queries run, I can see the rowID column in result DTSs. That is weird.

Any idea?

Sergiy SSC Guru Points: 110208 More actions · Answer 4

You missed the point:

When your procedure (or batch ) is being compiled

Run you full batch with Trace ON and you'll see - no statements are executed.

_____________
Code for TallyGenerator

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 5

Sergiy (6/18/2008)
You missed the point:
When your procedure (or batch ) is being compiled
Run you full batch with Trace ON and you'll see - no statements are executed.

So can you tell me how I am able to set/script Trace On in my script? I am using SQL Server 2000.

Thank you.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Easiest way - in QA menu click Query -> Show Server Trace.

_____________
Code for TallyGenerator

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 7

Sergiy (6/19/2008)
Easiest way - in QA menu click Query -> Show Server Trace.

When I comment out the last (the 3rd SELECT query that has the problem) and turn on the Trace as you said), I do not see the 2 nd and the 3rd query appear in my QA Trace Tab).

However, in my QA Results Tab, I see the following outputs:

personID petname rowID petnames

----------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)

(3 row(s) affected)

(1 row(s) affected)

personID petname rowID petnames

----------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

23 Fido 1 NULL

25 Shadow 2 NULL

23 Garfield 3 NULL

23 Casper 4 NULL

(4 row(s) affected)

So, can you tell me what the last output means in my QA Results tab?

Thank you.