?
Solved

SQL error: Cannot drop the table

Posted on 2014-08-21
8
Medium Priority
?
476 Views
Last Modified: 2014-08-25
The stored procedure below creates a temp table, and then tries to drop it, but when I try to exec I get the following error message;

(4 row(s) affected)
Msg 3701, Level 11, State 5, Procedure pReport_AgentProfitability, Line 35
Cannot drop the table '#tmpMap', because it does not exist or you do not have permission.

-- STORED PROCEDURE pReport_AgentProfitability
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('pReport_AgentProfitability') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE pReport_AgentProfitability
GO

-- ************************************************************************************************************      
-- CASE :
-- Description:     
-- Client ID:
-- ************************************************************************************************************ 
CREATE PROCEDURE [pReport_AgentProfitability]      
@pStartDate Datetime,      
@pEndDate Datetime,
@pReportType VARCHAR(50),     
@pPortfolioRegion int,   
@pMonthlyCostPerAccount MONEY,
@pCostOfFunds MONEY,   
@pAgentList VARCHAR(8000),      
@pSortBy    VARCHAR(100) = ''      
AS      
BEGIN      
    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED      
DECLARE @startDate DATETIME = @pStartDate       
DECLARE @endDate DATETIME = @pEndDate 
DECLARE @reportType VARCHAR(50) = @pReportType
DECLARE @portfolioRegion INT = @pPortfolioRegion
DECLARE @monthlyCostPerAccount MONEY = @pMonthlyCostPerAccount
DECLARE @costOfFunds MONEY = @pCostOfFunds
IF (@pAgentList ='') SET @pAgentList = null      
DECLARE @AgentsList TABLE      
(      
agentId int not null primary key      
)               
INSERT INTO @AgentsList select * from dbo.createnumbertable(@pAgentList)      
 --select * from @AgentsList      
    
IF EXISTS(SELECT null FROM tempdb..sysobjects WHERE NAME like '%tmpMap%')         
DROP TABLE #tmpMap            
    
-- Reference table containing GlAccountingMap     
CREATE TABLE #tmpMap        
(          
GlAccountTypeId INT NOT NULL,
AccountType VARCHAR(50),
GlAbstractTypeId INT,
AbstractType VARCHAR(50),
GlAccountingMapTypeId INT,
AccountingMapType VARCHAR(50),
GlAccountId INT,
AccountName VARCHAR(50),
GlAccountingMapModifierTypeId INT,
GlAccountingMapModifierType VARCHAR(50)    
)     


INSERT INTO #tmpMap
SELECT DISTINCT 
	act.GlAccountTypeId, act.Name AS AccountType, at.GlAbstractTypeId, at.Name AS AbstractType, amt.GlAccountingMapTypeId, 
	amt.Name AS AccountingMapType, a.GlAccountId, a.AccountName, ammt.GlAccountingMapModifierTypeId, ammt.Name AS GlAccountingMapModifierType
FROM  
	(
		SELECT DISTINCT 
			GlAccountingMapTypeId, GlAbstractTypeId, GlAccountId, GlAccountingMapModifierTypeId
		FROM 
			GlAccountingMap
	) am
	JOIN GlAbstractType at ON am.GlAbstractTypeId = at.GlAbstractTypeId
	JOIN GlAccount a ON am.GlAccountId = a.GlAccountId
	JOIN GlAccountType act ON a.GlAccountTypeId = act.GlAccountTypeId
	JOIN GlAccountingMapType amt ON am.GlAccountingMapTypeId = amt.GlAccountingMapTypeId
	JOIN GlAccountingMapModifierType ammt ON am.GlAccountingMapModifierTypeId = ammt.GlAccountingMapModifierTypeId

ORDER BY amt.GlAccountingMapTypeId, at.GlAbstractTypeId, act.GlAccountTypeId, a.GlAccountId, ammt.GlAccountingMapModifierTypeId


;WITH
cteTally10 AS (
		SELECT 0 AS tally UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
		SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally100 AS (
		SELECT *
		FROM (
			SELECT ROW_NUMBER() OVER (ORDER BY c1.tally) - 1 AS tally
			FROM cteTally10 c1 CROSS JOIN cteTally10 c2
		) x
		WHERE tally BETWEEN 0 AND DATEDIFF(MONTH, @startDate, @endDate)
),
cteEntities AS (
		SELECT DISTINCT e.PartyId
		FROM Entity e
		WHERE (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
)

SELECT 
	RT.*, 
	CONVERT(VARCHAR,DATEPART(MONTH,RT.PeriodDate))+'/'+CONVERT(VARCHAR,DATEPART(YEAR,RT.PeriodDate)) as Period,
	DT.*,
	RF.ReferralAmount,
	dtGL.InterestIncome, dtGL.FeeIncome, 
	dtGLWO.NETWriteOffs ,

	(
		SELECT COUNT(*) as countAcitve
		FROM Entity en
		left join CustomerAccount CuAc on en.PartyId = CuAc.AgentId
		WHERE
			en.PartyId = RT.PartyId
			AND CuAc.AccountCreateDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
			AND (CuAc.ArchiveDate IS NULL OR CuAc.ArchiveDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0))
			AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
	) as ActiveLoans,
	
	@costOfFunds / 100 as CostofFunds,
	
	( SUM(CASE WHEN dtADB.WhenBooked < RT.PeriodDate THEN dtADB.Amount END)
    +
      SUM(CASE WHEN dtADB.WhenBooked < RT.PeriodEndDate THEN dtADB.Amount END) 
	) / 2 AS AvgDailyBalance
	
FROM 
(
	/* The cross join places every month against every partyid, to facilitate a subsequent left join */
    SELECT
		c.PartyId, 
		DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally, 0) AS PeriodDate,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + t.tally + 1, 0) AS PeriodEndDate
	FROM 
		cteTally100 t CROSS JOIN cteEntities c
) AS RT

LEFT JOIN 
(
	SELECT
		en.PartyId,
		t.WhenBooked,
		ent.Amount
		
	FROM 
		Entity en
        JOIN CustomerAccount CuAc ON en.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
														WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById 
												   END)
        JOIN GlTransaction t ON t.PartyId = CuAc.PartyId 
        JOIN GlEntry ent ON ent.GlTransactionId = t.GlTransactionId
        JOIN GlAccount a ON a.GlAccountId = ent.GlAccountId

	WHERE a.AccountName = 'Accounts Receivable - Premiums'
          AND t.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, @endDate) + 1, 0)
          AND (@pAgentList IS NULL OR en.PartyId IN (select * from @AgentsList))
            
) AS dtADB ON RT.PartyId = dtADB.PartyId AND
	 dtADB.WhenBooked  < RT.PeriodEndDate

LEFT JOIN 
(
	SELECT 
		e.PartyId																	as PartyId,
		MAX(CA.AccountNumber)														as AcctNumber,
		DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0)						as createMonth,
		DATEADD(day, -1, DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn) + 1 ,0))	as endMonth,
		SUM(CASE WHEN PFL.IsOriginal = 1 THEN 1 ELSE 0 END)							as OriginalLoan,
		SUM(CASE WHEN PFL.IsOriginal = 0 THEN 1 ELSE 0 END)							as APLoan,
		COUNT(PFL.PremiumFinanceLoanId)												as AllLoansThisMonth,
		COUNT(DISTINCT CA.PartyId)													as AllAccounts,
		SUM(CASE WHEN PFL.IsOriginal = 1 THEN PFL.AmountFinanced ELSE 0 END)		as OriginalLoanAmountFinanced,
		SUM(CASE WHEN PFL.IsOriginal = 0 THEN PFL.AmountFinanced ELSE 0 END)		as APLoanAmountFinanced,
		SUM(PFL.AmountFinanced)														as AllLoansAmountFinanced,
		ROUND(AVG(CONVERT(FLOAT,(PFL.[DownPayment]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])) /
		    (PFL.[TotalPremium]-IP.[TotalEarnedTaxFees]-PFL.[EarnedBrokerFee])),4)	as AVGDownPaymentPercent,
		AVG(PFL.APR)																as AvgAPR,
		ROUND(SUM(CASE WHEN CA.PrimaryAccountStatusId = 2 THEN 1 ELSE 0 END) /
			CONVERT(FLOAT , COUNT(DISTINCT CA.PartyId)),4)							as CancelRatio
	    
	FROM 
		Entity e
		JOIN CustomerAccount CA ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CA.AgentId
													 WHEN @reportType = 'Submitted By' THEN CA.SubmittedById
												END)
		JOIN PremiumFinanceLoan PFL ON PFL.CustomerAccountId = CA.PartyId
		LEFT JOIN       
		(
			SELECT InPo.PremiumFinanceLoanId,SUM(InPo.EarnedTaxesFees) as TotalEarnedTaxFees
			FROM InsurancePolicy InPo
			GROUP BY InPo.PremiumFinanceLoanId
		) IP ON PFL.PremiumFinanceLoanId = IP.PremiumFinanceLoanId
		
	WHERE
		PFL.CreatedOn >= @startDate
		AND PFL.CreatedOn < DATEADD(DAY, 1, @endDate)
		AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
	    
	GROUP BY 
		e.PartyId,
		DATEADD(MONTH, DATEDIFF(MONTH, 0, PFL.CreatedOn), 0),
		DATEADD(day, -1, DATEADD(month, DATEDIFF(month,0,PFL.CreatedOn) + 1 ,0))
		
		
) DT ON DT.PartyId = RT.PartyId AND 
  DT.createMonth >=  RT.PeriodDate AND
  DT.createMonth < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)
  
LEFT JOIN
(
	SELECT
		e.PartyId, SUM(DISTINCT ReFe.Amount) as ReferralAmount, DATEADD(MONTH, DATEDIFF(MONTH, 0, ReFe.DatePaid), 0) as DatePaid
		
	FROM
		Entity e
		JOIN CustomerAccount CuAc ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
													   WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
												  END)
		JOIN ReferralFee ReFe ON CuAc.PartyId = ReFe.CustomerAccountId
		
	WHERE 
		--RF.ReferralFeeTypeId NOT IN (5,6) AND  -- Probably needed, but is not specified in the requirements
		ReFe.DatePaid >= @startDate
		AND ReFe.DatePaid < DATEADD(DAY, 1, @endDate)
		AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
		
	GROUP BY
		e.PartyId, DATEADD(MONTH, DATEDIFF(MONTH, 0, ReFe.DatePaid), 0)
		
) RF ON RF.PartyId = RT.PartyId AND
  RF.DatePaid >= RT.PeriodDate AND
  RF.DatePaid < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0) 
  
LEFT JOIN
(
	SELECT  
		e.PartyId, 	
		
-- Interest Income	
		(					
			SUM(CASE WHEN m.GlAccountingMapTypeId = 1								-- Customer Accounts
						  AND GLe.GlAbstractAccountId = 1							-- Accrued Interest
						  AND GLe.GlAccountingEventId = 11							-- New Loan
					 THEN GLe.Amount ELSE 0 
				END)  +
			
			SUM(CASE WHEN m.GlAccountingMapTypeId = 1								-- Customer Accounts
						  AND GLe.GlAbstractAccountId = 4							-- Non-Refundable Fee
					 THEN GLe.Amount ELSE 0 
				END) +	
				
			SUM(CASE WHEN m.GlAccountingMapTypeId = 8								-- Interest Accrual / Refund
						  AND GLe.GlAbstractAccountId = 1							-- Accrued Interest
					 THEN GLe.Amount ELSE 0 
				END)  +
			
			SUM(CASE WHEN GLe.GlAbstractAccountId = 1000							-- Manual
						  AND GLe.GlAccountingEventId = 38							-- Manual Transaction
						  AND a.GlAccountTypeId = 4									-- Income
						  AND (a.AccountName ='Finance Charge Income' OR a.AccountName ='Interest Income')
					 THEN GLe.Amount ELSE 0 
				END)			
		) * -1 
		as InterestIncome,

-- Fee Income
		(	
			SUM(CASE WHEN GLe.GlAccountingEventId IN (9,10)							-- Late Fee Assessed / Late Fee Waived
						  AND m.GlAccountingMapTypeId = 4							-- Late Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +
				
			SUM(CASE WHEN GLe.GlAccountingEventId IN (2,3)							-- Cancellation Fee Assessed / Cancellation Fee Waived
						  AND m.GlAccountingMapTypeId = 5							-- Cancellation Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +

			SUM(CASE WHEN GLe.GlAccountingEventId IN (36,37)						-- Misc. Fee Assessed / Misc. Fee Waived
						  AND m.GlAccountingMapTypeId = 15							-- Misc. Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +
				
			SUM(CASE WHEN GLe.GlAccountingEventId IN (16,17)						-- Reinstatement Fee Assessed / Reinstatement Fee Waived
						  AND m.GlAccountingMapTypeId = 6							-- Reinstatement Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) +
				
			SUM(CASE WHEN GLe.GlAccountingEventId IN (19,20)						-- Return Fee Assessed / Return Fee Waived
						  AND m.GlAccountingMapTypeId = 7							-- Return Fees
						  AND m.GlAbstractTypeId = 7								-- Income
					 THEN GLe.Amount ELSE 0 
				END) 
		) * -1
		as FeeIncome,
			
		DATEADD(month, DATEDIFF(month,0,GLt.WhenBooked),0) as WhenBooked
													
	FROM 
		Entity e
		JOIN CustomerAccount CuAc ON e.PartyId = (CASE WHEN @reportType = 'Agent' THEN CuAc.AgentId
													   WHEN @reportType = 'Submitted By' THEN CuAc.SubmittedById
												  END)
		JOIN GlTransaction GLt ON GLt.PartyId = CuAc.PartyId
		LEFT JOIN GlEntry GLe ON GLe.GlTransactionId = GLt.GlTransactionId
		LEFT JOIN #tmpMap m ON GLe.GlAbstractAccountId = m.GlAbstractTypeId
		LEFT JOIN GlAccount a on a.GlAccountId = GLe.GlAccountId
		
	WHERE 
		GLt.WhenBooked >= @startDate
		AND GLt.WhenBooked < DATEADD(DAY, 1, @endDate)
		AND (@pAgentList IS NULL OR e.PartyId IN (select * from @AgentsList))
		
	GROUP BY 
		e.PartyId, DATEADD(month, DATEDIFF(month,0,GLt.WhenBooked),0)
	
) dtGL ON dtGL.PartyId = RT.PartyId AND
		  dtGL.WhenBooked >= RT.PeriodDate AND
		  dtGL.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0) 
		  
LEFT JOIN
(
	SELECT  
		ent.PartyId, DATEADD(month, DATEDIFF(month,0,GLtr.WhenBooked),0) as WhenBooked,
-- NetWriteOffs
		(
			SUM(CASE WHEN (GLen.GlAccountingEventId = 15				-- Negative Values
						   AND GLen.GlAbstractAccountId = 8)
					 THEN GLen.Amount ELSE 0 
			END) + 
			
			SUM(CASE WHEN (GLen.GlAccountingEventId = 23				-- Positive Values
						   AND GLen.GlAbstractAccountId = 8)
					 THEN GLen.Amount ELSE 0 
			END)
		)	
		as NetWriteOffs
		
	FROM 
		Entity ent
		JOIN CustomerAccount CusAcc ON ent.PartyId = (CASE WHEN @reportType = 'Agent' THEN CusAcc.AgentId
														   WHEN @reportType = 'Submitted By' THEN CusAcc.SubmittedById
													  END)
		JOIN GlTransaction GLtr ON GLtr.PartyId = CusAcc.PartyId
		left join GlEntry GLen ON GLen.GlTransactionId = GLtr.GlTransactionId
		
	WHERE 
		GLtr.WhenBooked >= @startDate
		AND GLtr.WhenBooked < DATEADD(DAY, 1, @endDate)
		AND (@pAgentList IS NULL OR ent.PartyId IN (select * from @AgentsList))

	GROUP BY 
		ent.PartyId, DATEADD(month, DATEDIFF(month,0,GLtr.WhenBooked),0)
		
) dtGLWO ON dtGLWO.PartyId = RT.PartyId AND
		  dtGLWO.WhenBooked >= RT.PeriodDate AND
		  dtGLWO.WhenBooked < DATEADD(MONTH, DATEDIFF(MONTH, 0, RT.PeriodDate) + 1, 0)

GROUP BY RT.PartyId, RT.PeriodDate, RT.PeriodEndDate, 
		 DT.PartyId, DT.AcctNumber, DT.createMonth, DT.endMonth, DT.OriginalLoan, DT.APLoan, DT.AllLoansThisMonth, DT.AllAccounts, 
		 DT.OriginalLoanAmountFinanced, DT.APLoanAmountFinanced, DT.AllLoansAmountFinanced, DT.AVGDownPaymentPercent, DT.AvgAPR, DT.CancelRatio,
		 RF.ReferralAmount,
		 dtGL.InterestIncome, dtGL.FeeIncome,
		 dtGLWO.NetWriteOffs

ORDER BY RT.PartyId, RT.PeriodDate
/*
IF EXISTS(SELECT null FROM tempdb..sysobjects WHERE NAME like '%tmpMap%')         
DROP TABLE #tmpMap        
*/
END

Open in new window

0
Comment
Question by:pzozulka
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 500 total points
ID: 40277287
you need to replace

IF EXISTS(SELECT null FROM tempdb..sysobjects WHERE NAME like '%tmpMap%')        
DROP TABLE #tmpMap

with
IF OBJECT_ID('tempdb..#tmpMap') IS NOT NULL 
DROP TABLE #tmpMap

Open in new window

0
 
LVL 11

Expert Comment

by:David Kroll
ID: 40277576
The temporary table isn't going to exist there because you haven't created it yet.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40277587
David Kroll: What do you mean? That's what the IF statement is there for...only if it exists then drop it.
IF EXISTS(SELECT null FROM tempdb..sysobjects WHERE NAME like '%tmpMap%')         
DROP TABLE #tmpMap  

Open in new window


Habib Pourfard's way seems to work. Can someone explain why, and what the difference is?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Assisted Solution

by:David Kroll
David Kroll earned 500 total points
ID: 40277620
I'm just saying that it's unnecessary to try to drop the temp table before you have created it.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40278399
What the following query returns?

SELECT Name FROM tempdb..sysobjects WHERE NAME like '%tmpMap%'

Open in new window

0
 
LVL 8

Author Comment

by:pzozulka
ID: 40279237
Name
#tmpMap_____________________________________________________________________________________________________________0000000005E4
#tmpMap_____________________________________________________________________________________________________________000000000649
#tmpMap_____________________________________________________________________________________________________________00000000064C

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 40280698
David Kroll,

I'm just saying that it's unnecessary to try to drop the temp table before you have created it.
i would not insist, it is pointless.  It simply amazes me how many times I see that type of redundant code and sometimes with developers that have been doing it for years and should know better.   It usually means they were testing the SQL Script out in the query window where they needed it and did not know/did not care to remove it when they created the Stored Procedure.
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 40282508
Looks like you have 3 #tmpMap tables in your tempdb. Maybe you did a lot of tests and now there's "trash" in tempdb. I would suggest you a restart of SQL Server service when you can and try to run again your code after it.

Cheers
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

864 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