Dropping temporary tables - 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 2005 Forums

Transact-SQL (2005)

Dropping temporary tables

Author

Topic

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-10-11 : 12:01:02

I have written a T-SQL sproc that creates a temporary (#) table.

The sproc's code begins ALTER. Nothing unusual there.

I parsed the code, then used the execute button to ensure the ALTER takes effect.

I haven't run the code yet.

I then made some minor changes to the code, then parsed and executed again.

Still haven't run the code.

However when I parse and execute, I get error 2714

"There is already an object named '#table_name' in the database."

I have tried to drop the table using DROP TABLE #table_name only to be told "Cannot drop the table '#annuity_calculations', because it does not exist or you do not have permission."

Can anyone explain this and how to cure the problem?

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-11 : 12:48:04

close the query analyzer session and re-open it. It should clear the temp tables.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-11 : 12:53:02

Please post your complete SP here.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-10-12 : 04:36:23

Having rebooted the machine I still get the same problem.

Sorry, the code is confidential so can't be posted here.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 04:39:07

quote:
Originally posted by nmarks

Sorry, the code is confidential so can't be posted here.

Good luck!

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-12 : 04:45:51

Instead of using the same temp table, try using different tables

Madhivanan

Failing to plan is Planning to fail

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-12 : 04:55:02

i have had this before too. in the end i restarted the sql server service i.e. recreating tempdb

Em

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-10-12 : 05:20:09

I checked out this article first :http://support.microsoft.com/kb/827448 but even this solution didn't work. Even tried looping 10,000 times but that didn't work.

So I asked my boss and he got the answer straightaway. In brief, the code I had written was creating a table with either x or y number of columns depending on a variable's value. While this sounds like a reasonable idea, when the code gets compiled the compiler sees two identical CREATE statements.

Even thought the logical flow should mean only one or other CREATE statements are executed at runtime by means of an IF statement,
at compile time the compiler has no way of knowing what values the IF statements might take.

To prevent table duplication the compiler reports error message 3714. The solution was to do away with the IF statement and always create the table with the larger number of columns. This way the code has only one CREATE statement and compiled first time when I tried this.

Thanks to all those who offered constructive comments. I hope this thread will in future serve as a helpful reference to anyone experiencing the same problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 06:14:48

You mean like this pseudo-code? It took a while to understand what you have written in your code...

DECLARE	@Option TINYINT

SET @Option = 1

IF @Option = 1
	CREATE TABLE #Temp (i INT)
ELSE
	CREATE TABLE #Temp (i INT, j INT)

SELECT * FROM #Temp

E 12°55'05.25"
N 56°04'39.16"

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-10-12 : 07:13:06

try this.......

if object_id('tempdb..#TempObjects') IS NOT NULL
drop table #TempObjects

-- Temp table to store DISTINCT violating objects
create table #TempObjects
(
ObjectID int,
ObjectTypeID int,
hashbyte varbinary(20)
)

When solution is simple, God is answering….

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-12 : 07:22:45

hrishi,

That won't do it simply because the problem is with the perception of the compiler not with actual runtime.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-10-12 : 07:37:12

did u try that?

When solution is simple, God is answering….

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-10-12 : 07:40:05

spot on Harsh..... you r right......

When solution is simple, God is answering….

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-10-12 : 07:42:26

Peso : Yes your example explains the scenario well. All I know is that when I replaced the IF statemment (containing two CREATE TABLE statements with the same table name but different columns) with one CREATE statement and recompiled - it worked.

hrishi : Lovely footer to your messages "When solution is simple, God is answering…."

harsh : that is correct as fas as my understanding goes.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-12 : 08:15:26

quote:
Originally posted by nmarks

Peso : Yes your example explains the scenario well. All I know is that when I replaced the IF statemment (containing two CREATE TABLE statements with the same table name but different columns) with one CREATE statement and recompiled - it worked.

hrishi : Lovely footer to your messages "When solution is simple, God is answering…."

harsh : that is correct as fas as my understanding goes.

As I told you, use different tables


create procedure #t
as
DECLARE	@Option TINYINT

SET @Option = 1

IF @Option = 1
	CREATE TABLE #Temp (i INT)
ELSE
	CREATE TABLE #Temp1 (i INT, j INT)

IF @Option = 1

	SELECT * FROM #Temp
ELSE
	SELECT * FROM #Temp1

Madhivanan

Failing to plan is Planning to fail

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-10-12 : 08:36:41

spot on Harsh..... you r right......

When solution is simple, God is answering….

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-12 : 09:11:03

quote:
Originally posted by hrishi_des

spot on Harsh..... you r right......

When solution is simple, God is answering….

Duplicate reply

Madhivanan

Failing to plan is Planning to fail

nmarks
Yak Posting Veteran

53 Posts

Posted - 2007-10-12 : 10:39:22

Madhivanan

Try reading my reply again closely and you will, like everyone else, realise the problem has been solved and the technical reason for it arising in the first has been understood. There's no need for different tables, sound bites or emoticons.

Get over yourself.

X002548
Not Just a Number

15586 Posts

Posted - 2007-10-12 : 10:58:02

quote:
Originally posted by nmarks

Madhivanan

Try reading my reply again closely and you will, like everyone else, realise the problem has been solved and the technical reason for it arising in the first has been understood. There's no need for different tables, sound bites or emoticons.

Get over yourself.

Hey now, it ain't that easy to understand that you're writing scrub code

Got any column names in your 3rd party app that uses reserved words as column names?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Benholio
Starting Member

4 Posts

Posted - 2007-10-12 : 13:31:34

To accomplish the same as the original logic, I suppose you could have created the table first, then altered the table based on the logic of the if statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources