Stored Procedure Can't Drop Temp Table

[philhege]

(SQL2K SP3a standard on W2K)

Well, actually a little bit more than that. I have an SP that creates, manipulates, and drops a global temp table. When I try to put the SP in the database, I get

Server: Msg 2714, Level 16, State 1, Procedure gensp_GetShipmentDetail_new, Line 169
There is already an object named '##tmp_getshipmentdetail_tmp' in the database.


OK, so I should just issue a DROP TABLE, right? Now I get

Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '##tmp_getshipmentdetail_tmp', because it does not exist in the system catalog.

How can this be? I already tried the fix suggested in KB827448, to no avail. While this thread floats, I'm also calling PSS.

Anyone out there with the same problem and/or a fix?

TIA for your time and expertise.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[vongrunt]

When you try to drop ##table, does it exist in system catalogs select * from tempdb.dbo.sysobjects where xtype = 'U')?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[philhege]

Nope.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[vongrunt]

When ##table exists, it's definition should exist in tempdb system tables. This explains second error but not the first one.

AFAIK global temp tables get automatically dropped when they are no more referenced. Is it possible that first error is caused by development/debugging and server drops table shortly after that?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[philhege]

All I'm trying to do is turn the code into an SP. I understand that QA parses the code first, but there are no errors in the code itself. The first error occurs when I attempt to run the CREATE PROCEDURE code. The second occurs when I attempt to DROP TABLE (interactively).

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[ArtieChoke]

Does the sp get created even though it displays the error? I created a simple procedure that created, inserted, selected and dropped the global temp table and had no issues - both with dropping the connection after a query and keeping it open:

create procedure bozo
as

create table ##whatme (
	bogus	varchar(10)	null
)

insert into ##whatme values ('abc')

select * from ##whatme

drop table ##whatme
go

Does your create code have any embedded go statements in it?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[philhege]

No, the SP was not created. Turns out it's one of those "by design" quirks. I'm going to get a detailed explanation from Product Support Services on Monday, and will post it.

Short story is that you can't have multiple DDL-style actions on the same object in the same IF ELSE END block; it confuses the parser.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[miq]

Hi,
the next day you go to work just run the create procedure code don't test it first in QA. If you have test it in QA first then the global temp will remain in tempdb until you disconnect from sqlserver by closing QA ( and any global table referencing operation finishes).


B.R,
miq

 

[philhege]

I didn't test the code because it was a simple replacement of a couple of lines of existing code. I have ensured that the temporary table does not exist in tempdb. As I said previously, Product Support Services has confirmed that this is an issue with SQL Server 2000. I will post their explanation on Monday. I also requested that they create and post a KB article explaining the behavior and possible workarounds.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[philhege]

And, finally, here's the text of the explanation. Simply put, creating temp tables in if/else blocks confuses the parser.

PROBLEM DESCRIPTION

=====================

Customer is trying to create temp tables and populate the tables with criteria

-- it gives a error msg



ERROR MESSAGE

==============

Server: Msg 2714, Level 16, State 1, Procedure test, Line 9

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





Resolution:
=======================================================================

-- Its a creation of temp table inside if else and parser is not able to make out that the table does not exist

-- You referred to KB Creating New Database Objects Fails in a SQL Server Database (827448)

-- The requirement is to populate the table on the basis of conditions

-- referred to PRB: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714 (295305)

-- We tried WITH RECOMPILE

-- This talks about the same issue we are running into

-- It talks about the work around to create the tables outside the If else and alter it inside

-- Since this would not fit his requirement

-- Suggested that we do a select into with criteria 1=2 so that it creates a Empty table and then in If else for insert into with the criteria.

-- You did not want to create stored procedures since the first one is relatively easier

-- After we modified the stored Procedure it worked Fine.





References

============================

For the error we were getting this is one of the documents that you could refer to: Following KB articles may be of help with regards to this topic.



» 295305 PRB: Creating Temp Table Inside IF and ELSE Conditions Gives Error 2714

http://support.microsoft.com/?id=295305

» 305977 INF: Frequently Asked Questions - SQL Server 2000 - Table Variables

http://support.microsoft.com/?id=305977

» 44519 INF: Error Handling in Transact-SQL Blocks and Stored Procedures

http://support.microsoft.com/?id=44519

(With The explanation in this Kb article, we can correlate with the below limitation)



» Books Online : Transact-SQL Tips mentions this too as a limitation of Use of the temporary tables.

Minimizing the Use of Temporary Tables
Minimize the use of temporary tables as places to store intermediate results in a series of Transact-SQL statements. Some logic is too complex to perform in a single Transact-SQL statement. In these cases, you must code multiple Transact-SQL statements and use temporary tables to pass the results of one statement to the next. Creating and maintaining the temporary tables requires overhead; if possible, consider coding the operation as a single, more complex Transact-SQL statement.

In SQL Server 2000, use of temporary tables in stored procedures and triggers may cause the stored procedure or trigger to be recompiled every time it is used. To avoid such recompilation, stored procedures or triggers that use temporary tables must meet the following requirements:

In the stored procedure or trigger, all statements that contain the name of a temporary table must refer to a temporary table created in the same stored procedure. The temporary table cannot have been created in a calling or called stored procedure, or in a string executed using EXECUTE or sp_executesql.
All statements that contain the name of a temporary table must appear syntactically after its creation in the stored procedure or trigger.
The stored procedure or trigger cannot contain any DECLARE CURSOR statement whose SELECT statement references a temporary table.
All statements that contain the name of any temporary table must precede any DROP TABLE statement that references a temporary table. DROP TABLE statements are not needed for temporary tables created in a stored procedure; the tables are dropped automatically when the procedure terminates.
Statements creating a temporary table (such as CREATE TABLE or SELECT INTO) may not appear in a control-of-flow statement such as IF...ELSE or WHILE.
This is by design, and there can be only work around for this. How ever there are not many Knowledgebase articles with explanation. We thank you for bringing this to our notice. I will pass this on as a feedback and see that there is enough information available through KB articles too.


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[evilmousse]

if this is indeed an error in sql2002 and there is presumably no correct means of creating and distroying the table, maybe you can change your logistics so that the table always exists, and throw some logistics on it such that you set it to some detectable state that you can use to represent when it used to not exist. eg, deleting all rows instead of deleting the table, and checking to see if any rows exist when neccessary.

 

[evilmousse]

sql2000.
must.. preview.. posts..

 

[philhege]

I actually moved the SELECT ... INTO and DROP TABLE statements outside of the IF..THEN..ELSE blocks. Works just fine.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.