#temporary table drop - SQL Server Forums

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

SQL Server Development (2000)

#temporary table drop

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-22 : 08:42:45

Valter writes "I have problem dropping #temporary table...
When trying to execute code below in SQL2005 Management studio i get the following error:

Msg 2714, Level 16, State 1, Line 18
There is already an object named '#analitika1' in the database.

select top 10 *
into #analitika1
from analitika

select name, *
from tempdb..sysobjects
where name like '%analitika1%'

--returns 1 row...this is ok

drop table #analitika1

select name, *
from tempdb..sysobjects
where name like '%analitika1%'

--returns 0 rews as expected

select top 10 *
into #analitika1
from analitika

--this fails with error :There is already an object named '#analitika1' in the database.

Am I missing something or is this a bug?"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-03-22 : 08:44:48

This is a more reliable way to drop the temp table:

IF OBJECT_ID('tempdb..#analitika1') IS NOT NULL DROP TABLE #analitika1
select top 10 * into #analitika1 from analitika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-22 : 08:47:01

Also you should use Go after Drop statement

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-03-22 : 08:50:16

quote:
Originally posted by madhivanan

Also you should use Go after Drop statement

Madhivanan

Failing to plan is Planning to fail

Ja but GO won't work if it is in a Stored Proc.

Duane.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-22 : 17:16:52

You can't have use the same table name #analitika1 more than once within the query. Even though you have drop the table.
The erorr is not result of execution but error during query parsing.

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources