Error trying to remove a temporary table [closed]
Want to improve this question? Add details and make it clearer what problem is being solved by editing this post .
Closed 5 years ago .
improve this questionI own a service in which it performs data loading from an ERP into a DW. Being that the data source is SQL Server 2008 R2 10.50.1600.1 and the server target 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.
The temporary tables created by the query even after deletion remain loaded in the bank TempDB.
The query stays for hours running and not the load on the target bank.
Even after running the A command below the tables remain in the TempDB Bank:
IF EXISTS(SELECT * FROM Tempdb..SysObjects WHERE Name LIKE'%#TEMP%')
DROP TABLE #TEMP
The permission level has already been changed in if the problem remains.
Error displayed:
Cannot drop the table '# temp ' because it does not exist or you do not have permission. [SQLSTATE 42S02] (Error 3701).
2 answers
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;
It has already been deleted. To avoid the error, first check if the operation can be executed:
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END