Fred Schuff - 2007-05-23

I get the message "[SQL Server]Invalid object name '#tmptb1'." when running an SQL script through RexxSQL ODBC 2.5 to SQL Server. I am trying to use a temporary table (table names starts with "#" according to MS documentation to denote a temporary table and to use the tempdb database). Has anyone done this and if so, what do I change to make this work.

The SQLPREPARE returns a RC of 0. The error is returned in the SQLDESCRIBE or SQLOPEN.

The script looks like:

if (object_id('tempdb..#tmptb1')) > 0
begin
drop table #tmptb1
end

create table #tmptb1 (Cabinet_Name  Char(32),
                      LocX          Char(4),
                      LocY          Char(4),
                      Watts         Float )

insert into #tmptb1
   select in_name, in_loc_pos1, in_loc_pos2, ROUND(sum(pb_volts*pb_amps),1)
   from inventory, electric_circuit, pdu_breaker
  where in_cv_device_cat = 'CR'
    and in_status = 'AC'
    and in_id = ec_in_id
    and ec_pb_id = pb_id
    and pb_phase in (1, 2)
    and in_loc_bldg_cv = '580S' 
  group by in_name, in_loc_pos1, in_loc_pos2
  union ALL
select in_name, in_loc_pos1, in_loc_pos2, ROUND(sum(pb_volts*pb_amps*1.73),1)
   from inventory, electric_circuit, pdu_breaker
  where in_cv_device_cat = 'CR'
    and in_status = 'AC'
    and in_id = ec_in_id
    and ec_pb_id = pb_id
    and pb_phase = 3
    and in_loc_bldg_cv = '580S' 
  group by in_name, in_loc_pos1, in_loc_pos2
  order by in_name 

select Cabinet_Name, LocX, LocY, Watts, '1' as skip_type
   from #tmptb1
  union all
select 'Average Watts per Cabinet' AS Cabinet_Name, ' ' AS Locx, ' ' AS LocY,
        ROUND(AVG(Watts),1), '2' as skip_type
   from #tmptb1
  order by skip_type, Cabinet_Name