Problem:

SQL Server 2000 - global tables work, but need to use local temporary table and those are not working.

Resolution:

You need to use dynamic SQL and the EXECUTE IMMEDIATE statement to create the temporary table. Use something like the following:

declared a string-

       01 preptmp pic x(100).

move create statement into the string

            string

              'Create table #empbas2 (emp_id char(10), '

              ' name varchar(30))'

              INTO preptmp

            end-string.

used dynamic sql to execute the statement

            EXEC SQL

               EXECUTE IMMEDIATE :preptmp

            END-EXEC

then other embedded sql statements allow reference to the local temporary table

           EXEC SQL INSERT INTO #empbas2 values ('02','tom')

           END-EXEC

           EXEC SQL SELECT emp_id, name INTO :empid, :lname

                    FROM #empbas2

            END-EXEC