Can't Drop Tmp table in SQL Server?

iBinc
iBinc used Ask the Experts™
on
Trying to simply create and drop a #tmp table in sql server 2005 but getting error
Cannot drop the table '#tmp...', because it does not exist or you do not have permission.

I can drop other tables so I'm confused????
create table #tmp_test(temp_data INT);
drop table tempdb.dbo.#tmp_test;
 
Cannot drop the table '#tmp_test', because it does not exist or you do not have permission.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
try this:

if object_id('tempdb..#tmp_test') is not null
drop table #tmp_test

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
This would suffice..
No need to prefix tempdb.dbo here

create table #tmp_test(temp_data INT);
drop table #tmp_test;

Hope this helps
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
But it's better to follow chapmandew approach of checking it before issuing drop temp table.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
sorry already tried both ...does not work..same error

Author

Commented:
like I said, I can drop other tables with no problem, just not the #tmp table.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
If you try to execute this command within a single session or module, this should work fine.

create table #tmp_test(temp_data INT);
drop table #tmp_test;

If not kindly give your construct where you try to implement this.
Awarded 2008
Awarded 2008

Commented:
the examples work fine for me...are you posting your EXACT code?
what is the text of the error message?

Author

Commented:
exact code:

create table #tmp_epoch_test(unix_epoch_time INT);
insert into #tmp_epoch_test values(11824);

select dateadd(d, unix_epoch_time, '19700101')
from #tmp_epoch_test;

drop table #tmp_epoch_test;
oops! boo boo

Author

Commented:
I just ran exact same queries within a different instance of sql server 2005 and it worked! This is on a different PC.

Some kind of privilege issue...but why is it I can drop other tables in this instance?

odd
Awarded 2008
Awarded 2008

Commented:
no idea...but the syntax I gave you should work perfect for you....

Author

Commented:
Attributing my error to gremlins! But your answers were flawless anyway!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial