Drop temp table tied only to a particular session

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Drop temp table tied only to a particular session

Author

Topic

danelle10
Starting Member

2 Posts

Posted - 2006-06-14 : 21:39:00

Is it possible to drop a temp table tied only to that session?

Anyway, here's the scenario. I have created a temp table inside a condition in a trigger and drop it at the end of the trigger. Below is the sample script:

session 1:
create table #tab (field1 int)
insert into #tab values(1)
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#tab%')
DROP TABLE #tab

Note: The above command completed successfully

Then I run the first 2 lines of codes in the same session(i.e. session1).
This again completed successfully.

Then, I opened another session and run the codes below:
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#tab%')
DROP TABLE #tab

Below is the error I encountered:
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#tab', because it does not exist in the system catalog.

I am testing the above codes in two different sessions to simulate the scenarios wherein the same triggers would be executed at the same time using different sessions/connections.

But when I dropped the temp table in session1 and run the drop in session2, I didn't get the error anymore.

Is there a way to get around this problem? can we drop a temporary table that is only tied to that session?

Need your help and advise.

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-14 : 22:13:16

The problem is that query you are using to test if the temp table exists can find entries for other temp tables or other temporary objects. You can use the function in this link, F_TEMP_TABLE_EXISTS, to test the existence of a temp table.

Function to Check the Existence of a Temp Table:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67736


if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
	begin 
	drop table #temp
	end

Another thing you should consider is using a declared table, because it will go away as soon as the trigger goes out of scope.

declare @temp table (field1 int)

It will also prevent another problem you might have with a temp table in a trigger: If the stored procedure that makes the table update has a temp table with the same name, you will have a conflict. You should at least give the temp table in the trigger a long, complex name to reduce the chances of this. Something like:

#temp_C5B1DD40F0644751BAC50589E97B03AD

CODO ERGO SUM

danelle10
Starting Member

2 Posts

Posted - 2006-06-15 : 01:39:41

MVJ, Thanks for your reply.

Anyway, I also tried this one and it works now
if object_id('tempdb..#tab') is not null
DROP TABLE #tab

The above command only looks for the temp table in that connection unlike the one on my previous post that looks thru all the connections.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 09:37:52

quote:
Originally posted by danelle10

MVJ, Thanks for your reply.

Anyway, I also tried this one and it works now
if object_id('tempdb..#tab') is not null
DROP TABLE #tab

The above command only looks for the temp table in that connection unlike the one on my previous post that looks thru all the connections.

The reason I wrote that function was to get around the limitation of the method you just posted:

if object_id('tempdb..#tab') is not null 
	DROP TABLE #tab

The problem is that it cannot tell if the object is a temp table, procedure, or other temp object. I gave it a lot of thought, and I could think of no easier way to tell if it really is a temp table.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources