Altering and Updating Hash Table gives funny error

Question

Post reply

Altering and Updating Hash Table gives funny error

Harsha_ASP

SSC Enthusiast

Points: 117
More actions
August 12, 2004 at 10:59 am

#360673

HI,
I am getting a strange error with the following statements.
create table #temp ( col1 int, col2 int)
insert into #temp values (1,1)
alter table #temp add [01 Aug 2004] int
update #temp set [01 Aug 2004]=1
select * from #temp
Copy this T-SQL Query analyzer and press F5.
Hope u got the following error which I got
Server: Msg 207, Level 16, State 1, Line 6
Invalid column name '01 Aug 2004'.
Case 2: Run all the statements line by line, Hola!! You got the result . Successfully executed.
Case 3:
Add a Go in between these statements
alter table #temp add [01 Aug 2004] int
GO
update #temp set [01 Aug 2004]=1
Press F5, this works again !!
Why is it behaving like this? I want to use these statements in a stored procedure. But I cannot use Go in this situation in Sp right ??
Pls give me a solution ASAP..
Thanks in advance,
Harsha

HArsha

Viewing 4 posts - 1 through 3 (of 3 total)

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

MELVYN PATRICK LOPEZ Old Hand Points: 366 More actions · Answer 1

Hi:

My first impulse is to create the original table with the nullable third column...that would have been simple.

And then add the column names when you do the insert statement... then your update statement should go thru.

Now, if you must maintain the original table schema until the first insert, then your next option is to create a second #temp table:

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

create table #temp ( col1 int, col2 int)

---doing stuff in between

insert into #temp values (1,1)

---doing stuff in between

create table #temp2

(col1 int, col2 int, [01 Aug 2004] int null)

insert #temp2(col1, col2) select col1, col2 from #temp

---doing stuff in between

update #temp2 set [01 Aug 2004]=1

select * from #temp2

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

I hope this did not complicate your procedure.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

If you do

select * from tempdb..syscolumns where [id] = object_id('tempdb..#temp')

after your alter you will see the value has been updated to rflect the third column.

What I believe is happening is that the memory copy is not refreshed during the run but a check is occurring on the metadata layout between the two.

If you remove the update line and run the select will throw an error about a definition difference.

The reason the go works is it refreshes the in memory DDL as the first batch is finished and a new batch is begining and because you didn't drop the table and your connection is the same the table is still available.

I tried a couple of other ways as well and nothing seems to work around this. MS probably will call a feature but I would suggest doing a bug report based on in memory ddl metadata of temp table does not match ddl metadata for temp table in temdb after an alter during batch.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 3

I have modifed the code which is highlighted in red. I hope I understood the problem. Pls do adjust if it doesn't help you.

create table #temp ( col1 int, col2 int)

insert into #temp values (1,1)

alter table #temp add [01 Aug 2004] int null

update #temp set [01 Aug 2004]=1

select * from #temp

Thanks,

Ganesh