dropping a temp table

I've read here that a temp table is drop automatically when a store procedur
e
that created the temp table ends or when a session that uses a temp table
ends.
If I have a temp table that was created by an activex script inside a dts
and the dts is a scheduled job, would the temp table be dropped when the job
ended? Or should I explicitly drop the table in the script?
Thanks,
--
Dan D.IMHO, you should always drop what you create and never rely on the system to
clean up for you.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:19839D1E-FAEF-47CB-9047-987EEB3E0663@.microsoft.com...
> I've read here that a temp table is drop automatically when a store
> procedure
> that created the temp table ends or when a session that uses a temp table
> ends.
> If I have a temp table that was created by an activex script inside a dts
> and the dts is a scheduled job, would the temp table be dropped when the
> job
> ended? Or should I explicitly drop the table in the script?
> Thanks,
> --
> Dan D.|||Dan,
Better if we drop it in the sp / job.
if object_id('tempdb.dbo.#temp_table') is not null
drop table #temp_table
AMB
"Dan D." wrote:

> I've read here that a temp table is drop automatically when a store proced
ure
> that created the temp table ends or when a session that uses a temp table
> ends.
> If I have a temp table that was created by an activex script inside a dts
> and the dts is a scheduled job, would the temp table be dropped when the j
ob
> ended? Or should I explicitly drop the table in the script?
> Thanks,
> --
> Dan D.|||Is the stored procedure really going to see the #temp table if it is created
in the DTS package? I would guess they are separately scoped...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:AFF0DC56-3B04-4E8B-87FF-BFFD3BC9A352@.microsoft.com...
> Dan,
> Better if we drop it in the sp / job.
> if object_id('tempdb.dbo.#temp_table') is not null
> drop table #temp_table|||Aaron,
What I meant was that it is better to drop it before the code finishs.

> Is the stored procedure really going to see the #temp table if it is creat
ed
> in the DTS package? I would guess they are separately scoped...
Function Main()
sConnect="provider=sqloledb;server=weg-256;database=test_db;integrated
security=SSPI"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open sConnect
Conn.Execute ("create table #tmp (c1 int)")
Conn.Execute ("insert into #tmp values(1)")
Conn.Execute ("create procedure #p1 as set nocount on select * from #tmp if
object_id('tempdb.dbo.#tmp') is not null drop table #tmp")
Set Rs = Conn.Execute ("exec #p1")
msgbox Rs.fields.item(0).value
Conn.close
Main = DTSTaskExecResult_Success
End Function
AMB
"Aaron Bertrand [SQL Server MVP]" wrote:

> Is the stored procedure really going to see the #temp table if it is creat
ed
> in the DTS package? I would guess they are separately scoped...
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:AFF0DC56-3B04-4E8B-87FF-BFFD3BC9A352@.microsoft.com...
>
>|||Ah sorry, I saw "in the sp" but not " / job" (or didn't translate " / job"
to " / package/script")
A|||Thanks everyone. That's what I was always taught. Cleanup after yourself.
--
Dan D.
"Alejandro Mesa" wrote:
> Aaron,
> What I meant was that it is better to drop it before the code finishs.
>
> Function Main()
> sConnect="provider=sqloledb;server=weg-256;database=test_db;integrated
> security=SSPI"
> Set Conn = CreateObject("ADODB.Connection")
> Conn.Open sConnect
> Conn.Execute ("create table #tmp (c1 int)")
> Conn.Execute ("insert into #tmp values(1)")
> Conn.Execute ("create procedure #p1 as set nocount on select * from #tmp
if
> object_id('tempdb.dbo.#tmp') is not null drop table #tmp")
> Set Rs = Conn.Execute ("exec #p1")
> msgbox Rs.fields.item(0).value
> Conn.close
> Main = DTSTaskExecResult_Success
> End Function
>
> AMB
> "Aaron Bertrand [SQL Server MVP]" wrote:
>