SQL Server Performance Forum – Threads Archive

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 [email protected])=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 [email protected]
update Trans_Upd_Time_Exec_Log set NewExcTime = @lastTimeUpdated where [email protected]
end — Populate Table
select
[Function_id]
into
##Tmp_ID_Tbl5
from
Sys_Function
where (Function_created>[email protected] ) and
(Function_created<[email protected] ) 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,

---

Why do you use ## for temp table instead single #?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

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

---

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

---

]]>