Solved

SQL: Drop temp table if exists is returning error

Posted on 2014-10-08
1
664 Views
Last Modified: 2014-10-08
Error: There is already an object named '#tmpAnticipatedReleaseDate' in the database.

Before creating a temp table, I am checking if one already exists, but am receiving an error message, and don't know why.

IF OBJECT_ID('tempdb..#tmpAnticipatedRelease') IS NOT NULL                         
DROP TABLE #tmpAnticipatedReleaseDate 
GO  

-- Reference table containing anticipated release dates for all disbursements               
CREATE TABLE #tmpAnticipatedReleaseDate                
(                
DisbursementId INT NOT NULL,                
AnticipatedReleaseDate DATETIME               
)                
CREATE CLUSTERED INDEX cx ON #tmpAnticipatedReleaseDate(DisbursementId, AnticipatedReleaseDate)     


;WITH 
cteInstallmentHoldDueDate AS (
SELECT 
	InSc.InstallmentScheduleId, Insc.DueDate, 
	Di.DisbursementId, Di.Amount, Di.ReleaseDate, DiH.DisbursementHoldId, DiH.DisbursementHoldTypeId, DiH.HoldValue
FROM 
	Disbursement Di
	JOIN DisbursementHold DiH ON DiH.DisbursementId = Di.DisbursementId AND DiH.DisbursementHoldTypeId = 1
	JOIN InstallmentSchedule InSc ON InSc.CustomerAccountId = Di.PartyId AND InSc.InstallmentNumber = DiH.HoldValue
)

INSERT INTO #tmpAnticipatedReleaseDate (DisbursementId, AnticipatedReleaseDate)

SELECT Di.DisbursementId, Di.ReleaseDate
FROM 
	Disbursement Di

Open in new window

0
Comment
Question by:pzozulka
1 Comment
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40369361
Object name is slightly off in the IF:

IF OBJECT_ID('tempdb..#tmpAnticipatedReleaseDate') IS NOT NULL                        
    DROP TABLE #tmpAnticipatedReleaseDate
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question