3

I have created one stored procedure with 7 temporary tables and each temp table is dropped at the end of their own work.

I am calling the SP from one web service and same web service we are used for different instance.

I have dropped every temp table forcefully but when SP executes it will not delete any of the temporary table which are located in "tempdb/Temporary Table". And, when I open new instance of my application and try to execute same SP it will modify same temp tables.

This creates problem for me. it will lock the tables when SP execute simultaneously it will lock the table and my sp is not able to produce result and throw exception.

So I want to drop my temporary tables at the end of my operation. please help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brijesh Patel
  • 2,901
  • 15
  • 50
  • 73
  • 5
    Are you creating the tables in TempDB directly or are you using `create table #TableName...` or perhaps `create table ##TableName...`? – Mikael Eriksson Jul 08 '11 at 11:46
  • Maybe something in a try catch block is preventing the drop statements from executing? Need code. – JeffO Jul 08 '11 at 11:47
  • Simple solution make your temp table table variables. It sounds like you only want them active for a single batch anyway. – JStead Jul 08 '11 at 11:51
  • We have created temp tables by using "select * into #temp from mytablename". – Brijesh Patel Jul 08 '11 at 11:52
  • @Jstead - won't work if they are used in subprocs (due to scoping) or are large since stats don't work on table vars – JNK Jul 08 '11 at 11:57
  • **1.** It seems you are using the same connection all time. Can you check @@SPID result in procedure? **2.** To remove temptable, in beginning of your SP write next code IF OBJECT_ID('tempdb..#YourTableName') IS NOT NULL DELETE FROM #YourTableName ELSE CREATE TABLE #YourTableName – Dalex Jul 08 '11 at 12:22
  • 3
    Not sure why this question has been resurrected, but it's worth mentioning that you shouldn't **have** to do any cleanup at all. Per the [documentation](http://msdn.microsoft.com/en-us/library/ms174979.aspx): "A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished." – Damien_The_Unbeliever Jun 17 '13 at 06:57

4 Answers4

7

I can't tell you why this is happening, but I have dealt with it before as well. Try cleaning up your tables at the beginning or end of the SP or using table variables.

IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName
Jeremy Gray
  • 1,378
  • 1
  • 9
  • 24
3

This can occur in case if you have used many Temp tables and you have some Error in between of Your sp and your drop statement could not executed.
So its always best practice to use 

IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName

in start of SP.

Himanshu
  • 31,810
  • 31
  • 111
  • 133
Azhar Iqbal
  • 31
  • 2
1
IF object_id('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName

I think this will not work as we know sql server store temp table name with adding some extra character. if exists(select 1 from tempdb.sys.tables where name like '#TableName%') DROP TABLE #TableName

Scott Whitlock
  • 13,739
  • 7
  • 65
  • 114
Vikas P
  • 13
  • 3
1

To force dropping of temp tables use

BEGIN TRY DROP #MyTable END TRY BEGIN CATCH END CATCH

Ugly but effective. Use a separate TRY for each temporary table.

Scott Whitlock
  • 13,739
  • 7
  • 65
  • 114
WaitForPete
  • 417
  • 4
  • 5