Solved

There is already an object  Msg 2714, Level 16, State 6, Line 15

Posted on 2014-04-14
6
3,940 Views
Last Modified: 2016-10-05
Hi

I'm getting the following error

There is already an object  Msg 2714, Level 16, State 6, Line 15

There is already an object named '#LTIP_IDS' in the database


 
declare
	@p_maturity_date datetime,
	@p_ltip_ids varchar(1000),
	@p_user varchar(20),
	@rpt_type int

set @p_maturity_date = '2007-12-09'
set @p_ltip_ids = null
set @p_user = 'prodaa3'
set @rpt_type = 1
set @p_ltip_ids =[dbo].ufnParamOrNull(@p_ltip_ids)

create table #LTIP_IDS(LTIP_ID int)
insert #LTIP_IDS(LTIP_ID)	select convert(int,value)	from [dbo].fn_csvToLookupTable(@p_ltip_ids, default)

declare @award_year int, @cycle int
select @award_year= award_year, @cycle=cycle from [dbo].[fn_getYearCycle](@p_maturity_date)

--select PC_ID, BG_DESC, LOB_NAME, PC_NAME, BA_DESC
--into #PERMISSIONS 
--from [dbo].USER_BGS where AWARD_YEAR = @award_year	and CYCLE = @cycle	and  user_name = @p_user		


select 
	sa.EMPLOYEE_PROFILE_ID,
	ltip.CHILD_plan_short_name PLAN_SHORT_NAME, 
	replace(ltip.CHILD_plan_short_name, 'TU','')+ case when ltip.CHILD_plan_short_name like '%TU%' then 'TU' else '' end as PLAN_GROUP,
	ltip.ISSUE_DATE, 
	ltip.VAL_DATE, 
	ltip.EXPIRY_DATE,
	ltip.GRANT_PRICE,
	isNUll(DEFERRED_UNITS,
		(ltc.TOTAL_UNITS -ltc.FORFEITED_UNITS) * sa.DSU_DEFER_PCT / 100)
		 as matShareUnitsDeferred,

	sa.DSU_DEFER_PCT,
	sa.US_TAXPAYER,
	sa.CONFIRM_DT,
	sa.GROUPING,

	29 PLAN_CODE, 1 FUND_NO, case  when sa.US_TAXPAYER=1 then 4 else 2 end as ACCT_NO,
	PLAN_CODE as SU_PLAN_CODE,
	FUND_NO as SU_FUND_NO,
	ACCT_NO as SU_ACCT_NO
into #AWARDS
from   [dbo].[SU_MATURITY_SOR_VIEW] sa
	inner join [dbo].LONG_TERM_COMPENSATION ltc on sa.LTIC_ID = ltc.LTIC_ID	
	inner join [dbo].[LONG_TERM_INCENTIVE_PLAN_HIERARCHY_VIEW] ltip on ltip.LTIP_ID = ltc.LTIP_ID
	inner join [dbo].ACS_LTIP_MAP alm on ltip.LTIP_ID = alm.LTIP_ID 
	INNER JOIN #LTIP_IDS as tli ON (tli.LTIP_ID = (Case When @p_ltip_ids is null Then tli.LTIP_ID Else ltip.LTIP_ID End))
	left join [dbo].DEFERRAL_ELECTION de on ltc.LTIC_ID = de.LTIC_ID
where ltip.CHILD_plan_short_name not like 'VSU%' and  ltip.CHILD_plan_short_name not like 'OPT%'
	and sa.VAL_DATE = @p_maturity_date
	and ltc.TOTAL_UNITS -ltc.FORFEITED_UNITS>0




SELECT 
	a.EMPLOYEE_PROFILE_ID,
	eiv.B_AREA_DESC,
	eiv.EXEC_DESC,
	eiv.RL,
	eiv.MICRO,
	eiv.EMPLOYEE_ID,
	eiv.LAST_NAME,
	eiv.GIVEN_NAMES,
	eiv.POSITION,
	eiv.DIVISION,
	eiv.EP_COUNTRY_DESC,
	eiv.POS_CRNCY_SMBL,

	a.US_TAXPAYER,
	a.CONFIRM_DT,
	a.GROUPING,
	PLAN_SHORT_NAME, 
	PLAN_GROUP,
	ISSUE_DATE, 
	VAL_DATE,		
	DSU_DEFER_PCT,
	matShareUnitsDeferred,
	PLAN_CODE, 
	FUND_NO, 
	ACCT_NO,

	'000005533' AS CLIENT_NO, 
	RIGHT(REPLICATE('0',7) + eiv.EMPLOYEE_ID,7) + REPLICATE(' ',13) AS PARTICIPANT_ID, 
	RIGHT(REPLICATE('0', 5) + CONVERT(varchar(2), a.PLAN_CODE), 5) AS VPLAN_NO, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.ACCT_NO), 2) AS VACCT_NO, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.FUND_NO), 2) AS VFUND_NO, 
	CONVERT(varchar(8), a.ISSUE_DATE, 112) AS GRANT_DATE, 
	CONVERT(varchar(8), a.EXPIRY_DATE, 112) AS EXPIRY_DATE, 
	'01' AS VESTING_NO, 
	CONVERT(varchar(13),REPLICATE('0', 13 - LEN(REPLACE(matShareUnitsDeferred, '.', ''))) + REPLACE(matShareUnitsDeferred, '.', '')) AS DEFERRED_UNITS,
	CONVERT(varchar(9),REPLICATE('0', 9 - LEN(REPLACE(a.GRANT_PRICE, '.', ''))) + REPLACE(a.GRANT_PRICE, '.', '')) AS GRANT_PRICE, 
	CONVERT(varchar(9),REPLICATE('0', 9 - LEN(REPLACE(a.GRANT_PRICE, '.', ''))) + REPLACE(a.GRANT_PRICE, '.', '')) AS FMV_PRICE
	,
	RIGHT(REPLICATE('0', 5) + CONVERT(varchar(2), a.SU_PLAN_CODE), 5) AS SU_PLAN_CODE, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.SU_FUND_NO), 2) AS SU_FUND_NO, 
	RIGHT(REPLICATE('0', 2) + CONVERT(varchar(1), a.SU_ACCT_NO), 2) AS SU_ACCT_NO 

into #recordset
FROM
	#AWARDS a 
inner JOIN  EMPLOYEE_INFO_VIEW eiv on eiv.EMPLOYEE_PROFILE_ID = a.EMPLOYEE_PROFILE_ID and AWARD_YEAR=@award_year and CYCLE=@cycle
order by LAST_NAME,PLAN_SHORT_NAME


if @rpt_type = 1 -- vendor
select CLIENT_NO, PARTICIPANT_ID,VPLAN_NO,VACCT_NO, VFUND_NO,GRANT_DATE,EXPIRY_DATE
	, CONVERT(varchar(13),REPLICATE('0', 13 - LEN(REPLACE(sum(matShareUnitsDeferred), '.', ''))) + REPLACE(sum(matShareUnitsDeferred), '.', '')) as DEFERRED_UNITS
	, sum(matShareUnitsDeferred) matShareUnitsDeferred,
	SU_PLAN_CODE, 
	SU_FUND_NO, 
	SU_ACCT_NO
from #recordset
group by CLIENT_NO, PARTICIPANT_ID,VPLAN_NO,VACCT_NO, VFUND_NO,GRANT_DATE,EXPIRY_DATE,
	SU_PLAN_CODE, 
	SU_FUND_NO, 
	SU_ACCT_NO

else 
	select * from #recordset

	
drop table  #LTIP_IDS,  #AWARDS,  #recordset

Open in new window

0
Comment
Question by:FAH_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40000439
above the statement run
drop table '#LTIP_IDS
drop table #awards
drop table #recordset


if your query encountered errors it would never reach the bottom drop tables which is why you're getting that error.

you could always do a check to see if it exists as well before dropping it.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 400 total points
ID: 40000448
To conditionally delete that table if it exists..
IF OBJECT_ID('tempdb..#LTIP_IDS') IS NOT NULL
   DROP TABLE #LTIP_IDS

-- Your code goes here. 

Open in new window

0
 

Author Comment

by:FAH_
ID: 40000515
now same issue is coming for #awards
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 100 total points
ID: 40001607
You have to do it for all tables temporary tables.  (Any table starting with a #)
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40002635
Hi

For me good coding says to always check for temp table existence and drop it prior to creating it or selecting into.

I achieved some performance benefit on long etl procedure, but searching for the table from end of file, and after last reference of table doing an if table exists drop ...

The performance benefit was slightly smaller memory footprint for the procedure when it was running.

HTH
  David
0
 

Expert Comment

by:Vignesh S
ID: 41829792
Write the drop table query at the end of the stored procedure.
First run the drop temporary tables query("Drop Table Table_Name") separately (select appropriate query and press F5).
Then  Run the query from start to till the top of drop tmp table query("Drop Table Table_Name").
Now you won't get such an error
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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 the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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