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:
>

Drop tables with unknown names and unknown quantity

This is what I want to do:

1. Delete all tables in database with table names that ends with a
number.
2. Leave all other tables in tact.
3. Table names are unknown.
4. Numbers attached to table names are unknown.
5. Unknown number of tables in database.

For example:
(Tables in database)
Account
Account1
Account2
Binder
Binder1
Binder2
Binder3
......

I want to delete all the tables in the database with the exception
of Account and Binder.

I know that there are no wildcards in the "Drop Table tablename"
syntax. Does anyone have any suggestions on how to write this sql
statement?

Note: I am executing this statement in MS Access with the
"DoCmd.RunSQL sql_statement" command.

Thanks for any help![posted and mailed, please reply in news]

Amy (amarakunthy@.hotmail.com) writes:
> 1. Delete all tables in database with table names that ends with a
> number.
> 2. Leave all other tables in tact.
> 3. Table names are unknown.
> 4. Numbers attached to table names are unknown.
> 5. Unknown number of tables in database.

The simplest way is to say:

SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'

and then cut and paste and run the result. You would do this from
Query Analyzer.

If you would like to do it programmatically, because you are doing
it routinely, you could set up a cursor over sysobjects, and then
use dynamic SQL to drop the tables:

DECLARE @.tbl sysname
DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE name like '%[0-9]'
OPEN CURSOR drop_tbl_cur
WHILE 1 = 1
BEGIN
FETCH drop_tbl_cur INTO @.tbl
IF @.@.fetch_status <> 0
BREAK
EXEC ('DROP TABLE ' + @.tbl)
END
DEALLOCATE drop_tbl_cur

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I would also add ' AND xtype = 'U' ' in the where statement so that it
includes only user tables. This way it would include any object in the
statement and you would get errors when trying to execute.
it would look something like this:
SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9] and
xtype = 'U'

MC

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951EEFFFCC91AYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Amy (amarakunthy@.hotmail.com) writes:
> > 1. Delete all tables in database with table names that ends with a
> > number.
> > 2. Leave all other tables in tact.
> > 3. Table names are unknown.
> > 4. Numbers attached to table names are unknown.
> > 5. Unknown number of tables in database.
> The simplest way is to say:
> SELECT 'DROP TABLE ' + name FROM sysobjects WHERE name LIKE '%[0-9]'
> and then cut and paste and run the result. You would do this from
> Query Analyzer.
> If you would like to do it programmatically, because you are doing
> it routinely, you could set up a cursor over sysobjects, and then
> use dynamic SQL to drop the tables:
> DECLARE @.tbl sysname
> DECLARE drop_tbl_cur INSENSITIVE CURSOR FOR
> SELECT name FROM sysobjects WHERE name like '%[0-9]'
> OPEN CURSOR drop_tbl_cur
> WHILE 1 = 1
> BEGIN
> FETCH drop_tbl_cur INTO @.tbl
> IF @.@.fetch_status <> 0
> BREAK
> EXEC ('DROP TABLE ' + @.tbl)
> END
> DEALLOCATE drop_tbl_cur
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp