BUG: Stored Procedure with SELECT INTO May Fail with Error 207

  - Microsoft SQL Server version 4.2 for OS/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

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

   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

   Msg 207, Level 16, State 2:
   Invalid column name 'NEW_COLUMN'

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.