I use primary key in my temp tables since they hold many rows and I wanted to speed up the performance. Many of these stored procedures uses open query. These store proc are fired via scheduled DTS. The trouble is that they sometimes fail (I don't know why, maybe timeout issues), and the temp tables gets orphaned in tempdb. The temp tables have unique names w/in tempdb, but not the indexes. The next time the same sp runs, the pk name is already taken and the sp will fail every scheduled run thereafter. I could not figure out why the sp fails. Is there a way the temp table can have unique index names so that if the temp tables gets orphaned, it would not prevent the same sp from running again?
Unfortunately, the only way I can remove the orphaned temp tables is to restart the sql server.