Error trying to remove a temporary table

Question

I have a service where I load data from an ERP into a DW. The source of the data is SQL Server 2008 R2 10.50.1600.1 and the target server MS SQL Server 2008 10.0.5512.0 SP3.

The service performs a stored Procedure that generates temporary tables that feeds a physical table that generates the query that feeds the DW tables.

Temporary tables created by the query even after deletion remain loaded in the database TempDB.

The query is for hours running and not the load on the destination seat.

Even after executing the command below the tables remain in the Tempdb database:

IF EXISTS(SELECT * FROM Tempdb..SysObjects WHERE Name LIKE'%#TEMP%')
    DROP TABLE #TEMP

Already changed the permission level on whether to keep the problem.

Error presented:

Cannot drop the table '#temp' because it does not exist or you do not have permission. [SQLSTATE 42S02] (Error 3701).

Answer 1

Wagner, you need to test if it exists before deleting.

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#temp%') 
BEGIN
   DROP TABLE #temp;
END;

Answer 2

It has already been deleted. To avoid the error, check before if the operation can be executed:

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
   DROP TABLE #Temp
END

I put in the Github for future reference.