HELP!! Can't drop temp table

HELP!! Can't drop temp table

Post by Cand » Wed, 07 Jun 2000 04:00:00



Hope anyone can shed light on this problem...

I created a temporary table in a stored procedure, and the first time I ran it I
forgot the drop table at the end of the routine. Ordinarily, I would just
execute a DROP TABLE command on it, but that returns that there is no object of
that name in the System Catalog. The trouble is, I cannot now edit that stored
procedure because it will not compile, returning an error saying that the temp
table object already exists!!! I have deleted the stored procedure, and
recreated it, reboot my machine, and stopped and started the SQL server, but to
no avail. And it gets wierder. If I change the table name to something else, and
try to add the stored procedure, it says that that newly named temp teble
already exists too!!! And I haven't even run the procedure to execute the CREATE
TABLE....

I am at my wits end - can anyone help??

Thanks in advance

Andrew

 
 
 

HELP!! Can't drop temp table

Post by Tibor Karasz » Wed, 07 Jun 2000 04:00:00


Temptables are destroyed at end of execution of the proc (they are "local" to the
proc). So, I suspect that the problem is somewhere else in the proc-code. Especially
since you tried to specify some other temp-table name.

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.


Quote:> Hope anyone can shed light on this problem...

> I created a temporary table in a stored procedure, and the first time I ran it I
> forgot the drop table at the end of the routine. Ordinarily, I would just
> execute a DROP TABLE command on it, but that returns that there is no object of
> that name in the System Catalog. The trouble is, I cannot now edit that stored
> procedure because it will not compile, returning an error saying that the temp
> table object already exists!!! I have deleted the stored procedure, and
> recreated it, reboot my machine, and stopped and started the SQL server, but to
> no avail. And it gets wierder. If I change the table name to something else, and
> try to add the stored procedure, it says that that newly named temp teble
> already exists too!!! And I haven't even run the procedure to execute the CREATE
> TABLE....

> I am at my wits end - can anyone help??

> Thanks in advance

> Andrew


 
 
 

HELP!! Can't drop temp table

Post by Cand » Wed, 07 Jun 2000 04:00:00


On Tue, 6 Jun 2000 14:27:56 +0200, "Tibor Karaszi"


>Temptables are destroyed at end of execution of the proc (they are "local" to the
>proc). So, I suspect that the problem is somewhere else in the proc-code. Especially
>since you tried to specify some other temp-table name.

I am such a plonker (UKism) - I've only copied and pasted twice, so have 2
create tables in the same SP !!!

Thanks - you pointed me in exactly the right direction......

Andrew