Solved

SQL error

Posted on 2014-03-18
7
277 Views
Last Modified: 2014-03-18
Hi

I'm getting the following error:

Failed to retreive data from the database.
There is already an object named #userbgs in the database


declare 
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20)


SET @p_business_segment_id ={?@p_business_segment_id}
set @p_year ={?@p_year}
set @p_business_segment_id = dbo.ufnParamOrNull(@p_business_segment_id)
SET @p_user='quresf2'


select ba_id, bu_id, bg_id, lob_id, pc_id
into #userbgs
from user_bgs 
where award_year = @p_year and cycle = 12 and [user_name] = @p_user
 and ba_id = (Case When @p_business_segment_id is null Then ba_id Else  @p_business_segment_id  End)
	

SELECT  B_AREA_DESC , B_AREA, B_UNIT_DESC, [EXEC] Executive, RL Reporting_line, MICRO as Micro,MNGR_DESC, eiv.EMPLOYEE_ID, eiv.LAST_NAME, eiv.GIVEN_NAMES, 
[dbo].[fn_getEmployeeIncentivePlansForYear](eiv.EMPLOYEE_PROFILE_ID, eiv.AWARD_YEAR) as CASH_PLAN,	
[dbo].[fn_getEmployeeDeferralPlansForYear](eiv.EMPLOYEE_PROFILE_ID, eiv.AWARD_YEAR) as DEF_PLAN,
EXPAT_COUNTRY, EXPAT_TYPE, PS_START_DT,	
r.NAME AS REGULATOR, rr.NAME AS REGULATOR_RULE,  cs.CUSTOM_DEFERRAL_PCT, rr.DEFERRAL_PCT, cs.EFFECTIVE_DT, cs.COMMENTS as REGULATOR_COMMENTS
                     
FROM         dbo.CODE_STAFF AS cs INNER JOIN
                      dbo.REGULATOR AS r ON cs.REGULATOR_ID = r.REGULATOR_ID INNER JOIN
                      dbo.REGULATORY_RULE AS rr ON cs.REGULATORY_RULE_ID = rr.REGULATORY_RULE_ID inner join 
		EMPLOYEE_INFO_VIEW eiv on eiv.EMPLOYEE_PROFILE_ID=cs.EMPLOYEE_PROFILE_ID and eiv.AWARD_YEAR=@p_year and eiv.CYCLE = 12
		inner join #userbgs ubgs on ubgs.pc_id = eiv.PRODUCT_ID
left join
		( select rh.EMPLOYEE_PROFILE_ID, c.DESCRIPTION as EXPAT_COUNTRY, et.NAME as EXPAT_TYPE, PS_START_DT
			from  (select EMPLOYEE_PROFILE_ID,  max(PS_START_DT) PS_START_DT1 from
							RESIDENCE_HISTORY rh inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID	
						--where et.NAME not in ('Short-Term','Home')-- and PS_START_DT<'2011-04-01'
						group by EMPLOYEE_PROFILE_ID
				)rhm 
			inner join RESIDENCE_HISTORY rh on rhm.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID and PS_START_DT=PS_START_DT1 
			inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID	
			inner join dbo.COUNTRY c on c.COUNTRY_ID = rh.COUNTRY_ID
			
		)rh on eiv.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID

Open in new window

0
Comment
Question by:FAH_
  • 3
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 39936952
Are you getting this error when you first run the query or the second time?

You have a SELECT ... INTO clause on lines 13-14 that is creating a temporary table.
If you do not close the query window, the temporary table will stay in memory.

You need to do a DROP Table #userbgs prior to re-running the query.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39936964
>select ba_id, bu_id, bg_id, lob_id, pc_id into #userbgs
The above line creates the table, so if it already exists it will throw an error.

If your intention is to have this statement create a new table, add this block before.
IF OBJECT_ID('tempdb..#userbgs') IS NOT NULL
   DROP TABLE #userbgs

Open in new window

0
 

Author Comment

by:FAH_
ID: 39937005
Does the tempdb is the current db i am logged into ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:FAH_
ID: 39937014
I'm dropping the table at the end is that ok

declare
@p_year int,
@p_business_segment_id varchar(1000),
@p_user varchar(20)

set @p_user ={?@p_user}
SET @p_business_segment_id ={?@p_business_segment_id}
set @p_year ={?@p_year}
set @p_business_segment_id = dbo.ufnParamOrNull(@p_business_segment_id)



select ba_id, bu_id, bg_id, lob_id, pc_id
into #userbgs
from user_bgs
where award_year = @p_year and cycle = 12 and [user_name] = @p_user
 and ba_id = (Case When @p_business_segment_id is null Then ba_id Else  @p_business_segment_id  End)
      

SELECT  B_AREA_DESC , B_AREA, B_UNIT_DESC, [EXEC] Executive, RL Reporting_line, MICRO as Micro,MNGR_DESC, eiv.EMPLOYEE_ID, eiv.LAST_NAME, eiv.GIVEN_NAMES,
[dbo].[fn_getEmployeeIncentivePlansForYear](eiv.EMPLOYEE_PROFILE_ID, eiv.AWARD_YEAR) as CASH_PLAN,      
[dbo].[fn_getEmployeeDeferralPlansForYear](eiv.EMPLOYEE_PROFILE_ID, eiv.AWARD_YEAR) as DEF_PLAN,
EXPAT_COUNTRY, EXPAT_TYPE, PS_START_DT,      
r.NAME AS REGULATOR, rr.NAME AS REGULATOR_RULE,  cs.CUSTOM_DEFERRAL_PCT, rr.DEFERRAL_PCT, cs.EFFECTIVE_DT, cs.COMMENTS as REGULATOR_COMMENTS
                     
FROM         dbo.CODE_STAFF AS cs INNER JOIN
                      dbo.REGULATOR AS r ON cs.REGULATOR_ID = r.REGULATOR_ID INNER JOIN
                      dbo.REGULATORY_RULE AS rr ON cs.REGULATORY_RULE_ID = rr.REGULATORY_RULE_ID inner join
            EMPLOYEE_INFO_VIEW eiv on eiv.EMPLOYEE_PROFILE_ID=cs.EMPLOYEE_PROFILE_ID and eiv.AWARD_YEAR=@p_year and eiv.CYCLE = 12
            inner join #userbgs ubgs on ubgs.pc_id = eiv.PRODUCT_ID
left join
            ( select rh.EMPLOYEE_PROFILE_ID, c.DESCRIPTION as EXPAT_COUNTRY, et.NAME as EXPAT_TYPE, PS_START_DT
                  from  (select EMPLOYEE_PROFILE_ID,  max(PS_START_DT) PS_START_DT1 from
                                          RESIDENCE_HISTORY rh inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID      
                                    --where et.NAME not in ('Short-Term','Home')-- and PS_START_DT<'2011-04-01'
                                    group by EMPLOYEE_PROFILE_ID
                        )rhm
                  inner join RESIDENCE_HISTORY rh on rhm.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID and PS_START_DT=PS_START_DT1
                  inner join dbo.EXPAT_TYPE AS et ON rh.EXPAT_TYPE_ID = et.EXPAT_TYPE_ID      
                  inner join dbo.COUNTRY c on c.COUNTRY_ID = rh.COUNTRY_ID
                  
            )rh on eiv.EMPLOYEE_PROFILE_ID= rh.EMPLOYEE_PROFILE_ID

DROP TABLE #userbgs
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 39937025
>Does the tempdb is the current db i am logged into ?
No.  All temp tables ( # ) are stored in tempdb, and since you're in a different database, you need to prefix the DROP with tempdb..

>I'm dropping the table at the end is that ok
Fine, but you still need the block at the beginning of your code, as your initial problem is that the table exists when code is executed to create it, and having the DROP after that line does not solve the problem.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39937092
Thanks for the grade.  Good luck with your project.  -Jim
0
 

Author Comment

by:FAH_
ID: 39937138
Hi Jim

Could you look into another question of mine....

How would you concatenate a parameter value with a string

" ' " + @p_user+ " ' "
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xpath sql query 2008 8 41
SQL Query Conversion of IIF statement into CASE - Syntax issue 17 28
TSQL previous 5 21
SQL Pivot add row totals 2 0
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach 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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

932 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

12 Experts available now in Live!

Get 1:1 Help Now