Failed to access temp table

I create a DTS package, use one connection to create and populate temp table with ids to work with in next step, as following:
declare @lastTimeUpdated datetime, @lastTimeExec datetime
declare @tablename char(20)
set @tablename='Sys_Function'
select @lastTimeUpdated = max(Function_created) from Sys_Function
if (select count(*) from Trans_Upd_Time_Exec_Log where tablename=@tablename)=0
begin
insert into Trans_Upd_Time_Exec_Log (Tablename,NewExcTime,LstExcTime ) values(@tablename,getdate(),getdate())
select @lastTimeExec = getdate()
end
else
begin
select @lastTimeExec = LstExcTime from Trans_Upd_Time_Exec_Log where Tablename=@tablename
update Trans_Upd_Time_Exec_Log set NewExcTime = @lastTimeUpdated where Tablename=@tablename
end

-- Populate Table
select
[Function_id]
into
##Tmp_ID_Tbl5
from
Sys_Function
where (Function_created>=@lastTimeExec ) and
(Function_created<=@lastTimeUpdated )

And then I use another connection to delete rows to be updated where ids are in temp table
delete
from
Sys_Function
where
[Function_id] in
(
select id from ##Tmp_ID_Tbl5
)

When executing this step, it shows invalid object name '##Tmp_ID_Tbl5'.
Is anybody having any idea of this error message?

Thanks,

I want to create a global temp table.

Thanks,

Global temp tables are visible by all users... but Global temporary tables are automatically dropped when the session that created the table ends....

May be the connection created your global temp table not exists any more...


MohammedU.
Moderator
SQL-Server-Performance.com

quote:Global temporary tables are automatically dropped when the session that created the table ends....

Incorrect: a global temp table gets dropped when the last session that accesses the table ends.