Cannot drop temp table

Question

Cannot drop temp table

Shane Martin

SSC Veteran

Points: 253
More actions
December 3, 2007 at 8:24 am

#177094

I'm trying to drop a temp table with the following command-->
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#CustIDGroupIDPairs%' AND XTYPE = 'U')
DROP TABLE #CustIDGroupIDPairs
and am getting this error message-->
Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#CustIDGroupIDPairs', because it does not exist or you do not have permission.
I'm connected as sa so permissions shouldn't be a problem and the table does exist in sysobjects-->
select name, id, xtype, uid, crdate, *
from tempdb.dbo.sysobjects
where name like '%#CustIDGroupIDPairs%'
returns this-->
name,id,xtype,uid,crdate
#CustIDGroupIDPairs_________________________________________________________________________________________________0000000019A7,447259368,U ,1,2007-08-07 07:20:01.237
However, when I attempt to select from the table I get this error-->
Msg 208, Level 16, State 0, Line 1
Invalid object name '#CustIDGroupIDPairs'.
It's almost like the table entry is "stuck" in sysobjects but doesn't exist otherwise. Has anyone else ran into a a similar problem, any suggestions?
Thanks,
--Shane

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

What makes you think the table is stuck?

The table only exists for the connection that made the temp table, and should go away when that connection is closed or the table is dropped inside the connection.

Shane Martin SSC Veteran Points: 253 More actions · Answer 2

Is there a way to tell which connection it was created under? It was either A) either created by a scheduled job which also should have dropped it or B) created by me in Query Analyzer in which case it should've been dropped when I closed QA.

Lowell SSC Guru Points: 323518 More actions · Answer 3

you want to drop only the tables that are related to your connection; your IF EXISTS statement checks accross all connections, so you could pick up a temp table from a different window/connection that you don't want to affect.

the MSDN/books online suggests doing it this way:

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL

DROP TABLE #temptable;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shane Martin SSC Veteran Points: 253 More actions · Answer 4

thanks, I'll use your if exists statement in my stored procedure.

I'm still a little confused about how the temp table stuck around, there shouldn't be any open connections related to that table, but I guess there must be if it's still around...

Anders Pedersen SSChampion Points: 11410 More actions · Answer 5

I don't think in 13 years of being a DBA seen one stick around. Besides even if it does is it really a problem? Worst case it will be gone the next time SQL is restarted. Maybe if it was a huge temp table it could be an issue.

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 6

I tend to agree with Anders here. I've not seen them stick around.

More, I'd find the connection that is causing the issue and terminate it. The table should disappear. Also, track down what's wrong.

Najm Hashmi SSC-Addicted Points: 484 More actions · Answer 7

Shane Martin (12/3/2007)
thanks, I'll use your if exists statement in my stored procedure.
I'm still a little confused about how the temp table stuck around, there shouldn't be any open connections related to that table, but I guess there must be if it's still around...

Hi Shane,

If I remember correctly, SQL Sever keeps the metadata of temp table when it expects that it will be reused. When next time a user process recreates it, SQL Server does not have to do all required work.

I hope it helps.

-Najm