Microsoft KB Archive/113346

{|
 * width="100%"|

The information in this article applies to: - Microsoft SQL Server version 4.2 for OS/2 BUG# OS/2: 1829 (4.2)

SYMPTOMS
Execution of a stored procedure which creates some temporary tables using SELECT INTO and references a column in one of the newly created temporary tables may fail with an error message 207:

Invalid column Name NOTE: The conditions under which the above error occurs is described in the More Information section at the end of this article.

WORKAROUND
Break the SELECT INTO statement into two statements: a CREATE TABLE and a INSERT INTO:

Example:

select 'NEW_COLUMN'=COL1 into #temp2 from #temp1 can be broken into create table #temp2 (NEW_COLUMN float) insert into #temp2 select COL1 from #temp1

STATUS
Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
The following are the condition that will produce the error:

For example:

create procedure PROC1 as      select * into #temp1 FROM MYTABLE select 'NEW_COLUMN' = COL1 into #temp2 from #temp1 select NEW_COLUMN from #temp2 The above stored procedure may sometimes fails with the error message: Msg 207, Level 16, State 2: Invalid column name 'NEW_COLUMN' when the following conditions are satisfied:
 * 1) The stored procedure should select into a temporary table(#temp1) from a real table. From this temporary table (#temp1), it should select into another temporary table(#temp2) and in the process name a column ('NEW_COLUMN'=COL1). It should reference this named column (NEW_COLUMN).
 * 2) The server must be case sensitive (For example: code page 850, binary sort order).
 * 3) The sever must be restarted after the creation of the stored procedure and before the execution of this (PROC1) stored procedure.
 * 4) A stored procedure which creates some temporary table(s) (for example: sp_MSbackup_now) should be executed before the execution of this (PROC1) stored procedure.
 * }

-

Last reviewed: April 29, 1997

© 1998 Microsoft Corporation. All rights reserved. Terms of Use.