Invalid column name 'ID' SQL error

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 'ID' SQL error

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-16 : 10:50:35

Hi,
I have a sample query as follows. However, I do not know what happens when I run the scripts, I got error like "Server: Msg 207, Level 16, State 3, Line 21
Invalid column name 'ID'."


create table #t ([Stats] int, Statname varchar(50))
declare @i int, @rows int, @Statname varchar(50)


set @rows=4

insert #t
select 54 , 'MEMBERS'
union all select 585 , 'PRESENTATION'
union all select 1083 , 'VIEWS'
union all select 1138, 'VIEWlOGS'

alter table #t add [ID] int identity(1,1)

select * from #t


set @i=1
while @i <= @rows
	begin
		select @StatName= StatName from #t where [ID]=@i
		
		set @i=@i+1
	end


--
drop table #t

If I comment out the script block , then the error disappears ! And the other scripts work .


create table #t ([Stats] int, Statname varchar(50))
declare @i int, @rows int, @Statname varchar(50)


set @rows=4

insert #t
select 54 , 'MEMBERS'
union all select 585 , 'PRESENTATION'
union all select 1083 , 'VIEWS'
union all select 1138, 'VIEWlOGS'

alter table #t add [ID] int identity(1,1)

select * from #t

/*
set @i=1
while @i <= @rows
	begin
		select @StatName= StatName from #t where [ID]=@i
		
		set @i=@i+1
	end
*/

--
drop table #t

Thanks in advance for your help.
johnsql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-16 : 10:57:49

Move the insert and alter table statement before the declare and insert a GO. The Parser can't find the column [ID] in the table #t

KH
[spoiler]Time is always against us[/spoiler]

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-16 : 11:11:18

quote:
Originally posted by khtan

Move the insert and alter table statement before the declare and insert a GO. The Parser can't find the column [ID] in the table #t

KH
[spoiler]Time is always against us[/spoiler]

Thanks. The problem is solved. However, I have 3 questions.
1/ Why the orders of 'create', 'alter', 'insert' are important in this case?
Why 'alter' statement must go before 'insert' statement?


   create table #t ([Stats] int, Statname varchar(50))

   alter table #t add [ID] int identity(1,1)

   GO

2/ Why is 'GO' needed?

3/ If 'Alter' must sit on the very top of the scripts, then I cannot alter the temp table #t dynamically. In otherwords, I cannot make 'atler' statement any where I want?

Thanks.
johnsql

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-16 : 13:15:03

It is not recommended to alter temp tables at all.

It is a quite complicated question, SQL server is a mixture of compiler and interpreter, and it also uses some kind of 'early' and 'late' name binding.

Even worse, SQL 2000 and SQL 2005 behaive differently on the same script.

So, if you dont want to trouble the trouble, add the damned identity column in advance in the create table #t statement

johnsql
Posting Yak Master

161 Posts

Posted - 2007-11-16 : 14:22:28

quote:
Originally posted by evilDBA

It is not recommended to alter temp tables at all.

It is a quite complicated question, SQL server is a mixture of compiler and interpreter, and it also uses some kind of 'early' and 'late' name binding.

Even worse, SQL 2000 and SQL 2005 behaive differently on the same script.

So, if you dont want to trouble the trouble, add the damned identity column in advance in the create table #t statement

Thanks for your explanation. But, in scenario of dynamic column adding, late naming binding is required even I understand my question is quite complicated?

johnsql

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-16 : 14:54:07

Actually, I have never seen that question completely explained in the Books Online.

Even it is late binding, it is not a run-time binding.
Also, some parts of the procedure could be bound differently
And sometimes SQL server does not understand when it need to 'recompile' the rest of the procedure.

Just few examples:

exec('create table #a (n int)')
select * from #a
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '#a'.

SQL server is not aware what is done in exec()

if 1=2 create table #t (a int)
else create table #t (b int)
select * from #t
Server: Msg 2714, Level 16, State 1, Line 2
There is already an object named '#t' in the database.

Compiler is not aware of IFs and finds CREATE TABLE twice

If SQL server was 100% interpreter, all such queries would work
But it tries to compile as much as possible

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources