Solved

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

Posted on 2014-04-14
6
3,726 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_
6 Comments
 
LVL 39

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 35
Counting Distinct values in two columns 3 14
How to calculate iops? 12 27
Numeric sequence in SQL 14 36
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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now