Error trying to remove a temporary table [closed]

Navigation
3

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

The service executes 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 are kept loaded in the TempDB database.

The query is running for hours instead of the load on the target bank.

Even after executing the command a below the tables remain in the TempDB database: 

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

The permission level has been changed in if the problem persists.

Error presented:

  

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

    
asked by anonymous 18.01.2016 / 13:26

2 answers

5

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;
    
18.01.2016 / 13:31
4

It has already been deleted. To avoid the error, first check whether the operation can be performed: 

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
   DROP TABLE #Temp
END
    
18.01.2016 / 13:30
How to not allow repeated INSERT giving reload on the page? [duplicate] Button within an A (Link)? Is this a bad practice?