INSERT, ALTER TABLE, doesnt recognize new field?

[SJG0526]

Here's my stored procedure in SQL Server 2000:

CREATE PROCEDURE [spGetClients] @CurrentPage int, @PageSize int, @TotalRecs int output AS

select * into #TempTable from clients where 1 = 2
alter table #TempTable add id int IDENTITY PRIMARY KEY
--fill the temporary table with data
INSERT into #TempTable
SELECT *
FROM Clients

SELECT *
FROM #TempTable
where id > 3

--return total # recs available as an output parameter
SELECT @TotalRecs = COUNT(*) FROM Clients
GO

Records are inserted ok into #TempTable and each record is assigned a unique ID (the new field added). My problem is that the SELECT * FROM #TempTable WHERE id > 3 gives me an error "Invalid column name 'id'".

Is this a SQL bug? If I take out the WHERE clause on the SeLECT statement, it shows me the records with their ids.

Thanks!

 

[JayKusch]

Ran into the same issue a few months ago. Never found the issue and chalked it up as a SQL Server bug.

Thanks

J. Kusch

 

[JayKusch]

Here is the post ...

thread183-772261

If you figure a way ... Please let us know!

Thanks

J. Kusch

 

[vongrunt]

I'm not sure this is exclusively problem w/ identities on temp tables:

create procedure blah
as
create table #tmpTable
(	some_text varchar(32)
)
ALTER TABLE #tmpTable  ADD some_int int
insert into #tmpTable (some_text) values ('Blah 1')
insert into #tmpTable (some_text) values ('Blah 2')
select some_int  from #tmpTable 
GO

Exec blah returns "Invalid column name 'some_int'".

Anyway, this works:

select  identity(int, 1, 1) as id_key, * 
into #TempTable
from Clients

 

[delphidestructor]

found a way to make this work. if anyone hasn't yet and are still interested.

Mike

 

[vongrunt]

It was 5 months ago... shoot anyway

 

[delphidestructor]

Just found the post today. I was having the same problem this morning. I haven't been woking on the problem for 5 months.

Mike

 

[RickJ]

Sorry, four more months have passed, and I just found this thread today.

It's the closest thing I can find to the problem I'm having, which is simply that I can't use my temporary table after bulk inserting into it. I get the following errors:

"
Server: Msg 207, Level 16, State 3, Procedure DT092, Line 59
Invalid column name 'AccountGroup'.
Server: Msg 207, Level 16, State 1, Procedure DT092, Line 59
Invalid column name 'Responsibility'.
"


Here's the temporary table declaration:

"
CREATE TABLE #VAXAccounts (
Account char (4) NULL,
AccountDesc char (30) NULL,
AccountGroup char (2) NULL,
AccountType char (1) NULL,
Responsibility char (6) NULL,
ElementRequired char (1) NULL,
AccountStatus char (1) NULL)
"

And the bulk insert:

"
BULK INSERT #VAXAccounts
FROM 'UT155_ACCOUNTS.DAT'
WITH (FieldTerminator = '~', MaxErrors = 0);
"


I first got the error when I attempted to insert the value into a table variable, but then while "debugging" I simplified the problem and found that even a "SELECT * FROM #VAXAccounts" gives the same error messages.

What baffles me is that I've used this technique over a dozen times (I have a series of procedures to verify the text files generated on our old Interbase system on a VAX against our new SQL Server system on) with no problems.

Also, I have no idea why two columns in particular are singled out.

Lastly, here's a row from the text file that gets inserted into the temporary table with no problem but then causes an error when I try to do anything with the temporary table:

"
1234~SAFETY ~73~O~131000~Y~I
"

Any help would be much appreciated (not sure if I should have made this a new thread or not)