#temp table/Alternate Key issue...

[DLu]

Hi:
I created following temp table in a proc as
create table #tempOutput
(objID int identity (1,1) primary key clustered,
DriverID varchar(10) not null,
StatusID int not null
constraint AK_tempOutput_DriverID_StatusID
unique (DriverID, StatusID),
Note varchar(50))

I run the proc first time ok. but the second time got the error:
AK_tempOutput_DriverID_StatusID exists already....

after trace to tempdb -----
select * from tempdb..sysobjects
where name = 'AK_tempOutput_DriverID_StatusID'

name id xtype parent_obj
AK_tempOutput_DriverID_StatusID 849652640 UQ 833652583

select * from tempdb..sysobjects where ID = 833652583
Now, the temp table tempOutput becomes
#tempOutput_____________________________0000000002 A1

But I could not drop this table in tempDB,
and I could restart the SQLserver to clean the tempdb at this time.

after I renamed the AK_tempOutput_DriverID_StatusID to AK_tempOutput_DriverID_StatusID_1 in the create sql statement,
the proc recreated and exec works.

Is there a way without restart SQL server to clean the Alternate key?
Is there a way without restart SQL server to clean the temp table?
Also, how to prevent the issue?
This happens in both SQL2005 ent and SQL2000 ent.
I think the exec proc ending should drop the #temp automatically and thus corresponding PK/AK?

thanks
David

[skhanal]

You can only drop a temp table you created.

You can drop it as any table like

drop table #tempoutput

You are right - Temp table created by a stored procedure is dropped by sql server when execution completes.

Can you post your stored procedure code

[nosepicker]

Like skhanal said, temp tables should get dropped when the procedure finishes, but it is good practice to put a DROP TABLE command at the end of the proc anyway. Once the table is dropped, all keys associated with it get dropped too. The problem you experienced could be due to a hanging transaction, or a database connection not being closed properly.

[DLu]

Hi skhanal/nosepicker:

thanks for your reply.
I will include drop table statement at the ending of proc.

The #tempOutput__________0000000002A1 is still in the TempDB temporary table section. I agree that it might be the by-product of a pending transaction or network connection error, but still, this table #tempOutput__________0000000002A1 and its object of AKxxxx which caused the problem, SHOULD NOT be in the tempdb if it is due to connection issue.

thanks
-D