There is already an object named '???????' in the database.

Not exactly sure why but the error I am getting I do not understand. I was testing this code and got the error on....
Msg 2714, Level 16, State 6, Line 40
There is already an object named '#SalesSummary' in the database.

I thought I would have to have that value there......NO?

USE [DATA_04]
Declare @Cus_no_Start varchar(12),
	 @Cus_no_End varchar(12), 
	 @DB_Include int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 

----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate 	= CurrWeekEndDate, 
   @CurrToDateYTD 	= CurrToDateYTD,
   @CurrFromDateYTD	= CurrFromDateYTD,
   @CurrFromDate 	= CurrFromDate,
   @CurrToDate 		= CurrToDate, 
   @CurrToDateYTD 	= CurrToDateYTD,
   @CurrWeekBeginDate 	= CurrWeekBeginDate,
   @CurrWeekEndDate 	= CurrWeekEndDate,
   @PriorWeekBeginDate 	= PriorWeekBeginDate,
   @PriorWeekEndDate 	= PriorWeekEndDate,
   @PriorFromDateYTD 	= PriorFromDateYTD,
   @PriorToDateYTD 	= PriorToDateYTD,
   @PriorFromDate 	= PriorFromDate,
   @PriorToDate 	= PriorToDate
from dbo.Rpt_ReportingDates
declare @CurrentWeek decimal(8,0), @NumberOfWeeks int, @CurrToDate_Period tinyint, @CurrToDateYTD_FiscalYear smallint
select 
   @CurrentWeek 	= CurrentWeek,
   @NumberOfWeeks 	= NumberOfWeeks,
   @CurrToDate_Period 	= CurrToDate_Period,
   @CurrToDateYTD_FiscalYear = CurrToDateYTD_FiscalYear
from dbo.vwFiscalDates

------------------------------------------------------------
-- create temp table for Sales Summary
------------------------------------------------------------

Create table #SalesSummary 
(
	[data_source] varchar(12),--  what database is the data coming from  macola or rams?
	PerType varchar(18) NULL,
	[IDVALUE] Varchar (6) Null,
	[ID_TITLE]Varchar (40) Null,
	[CurrWeekEndDate] [int] NULL ,
	[CurrToDate] [int] NULL ,
	[CurrToDateYTD] [int] NULL ,
	[CurrentWeek] [decimal](8, 0) NULL ,
	[NumberOfWeeks] [int] NULL ,
	[CurrToDate_Period] [tinyint] NULL ,
	[CurrToDateYTD_FiscalYear] [smallint] NULL ,	
	[Terr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Terr_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Report_Sort] [int] NULL ,
	[BrandCode] Varchar (1) null,
	[TerrAdopted] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TerrAdoptedName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Adopted_Sort] [int] NULL ,
	[BRAND] Varchar (100) Null,
	[BRAND_ID] Varchar (6) Null,
	Item_no varchar(15),
	Gross_Sales decimal(38,2) NULL,
	Cost decimal(38,2) NULL
)

----------------------------------------------------
-- insert for MTD Current and Prior MACOLA
----------------------------------------------------
if (@DB_Include = 0 or @DB_Include = 2) 
BEGIN
INSERT INTO #SalesSummary 

SELECT DISTINCT

	'Macola',
	'2 - MTD Current',
	Rpt_Territory.RAMSID as IDVALUE, -- IDVALUE
	'0' AS ID_TITLE, -- ID_TITLE
	@CurrWeekEndDate as CurrWeekEndDate, 
    @CurrToDate as CurrToDate, 
    @CurrToDateYTD as CurrToDateYTD, 
    @CurrentWeek AS CurrentWeek,
    @NumberofWeeks AS NumberofWeeks,
    @CurrToDate_Period AS CurrToDate_Period,
    @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
    ARCUSFIL_SQL.Terr as Terr,
	Rpt_Territory.Terr_Name as Terr_Name,
	Rpt_Territory.Report_Sort as Report_Sort,
 	Case when Rpt_TerritoryAdopted.RAMSID is null then 'A'
		Else 'B' end as BrandCode,
    Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
    Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
    Rpt_Territory.Report_Sort as Adopted_Sort,
	PR_ID.ID_DESC AS BRAND,
	PD.PRD_R_BRANDNAME AS BRAND_ID,
	OELINHST_SQL.Item_no, 
	Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate  
         THEN sls_amt ELSE 0 END),
	Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate 
         THEN cost_amt ELSE 0 END)
  
       
FROM OELINHST_SQL
	INNER JOIN OEHDRHST_SQL 
		ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type 
		AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no 
		AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
	INNER JOIN ARCUSFIL_SQL 
		ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no

	INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = ARCUSFIL_SQL.Terr

/********************/-- Here is where I pick up IDVALUE from Ross using Macola Item number
	LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL 
		ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
           INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = '0' + IMITMIDX_SQL.Item_no   
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
/********************/--

	LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted 
		ON OELINHST_SQL.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
		AND Rpt_TerritoryAdopted.End_Item_no

	
--WHERE  	(@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start 
		--and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY 
	Rpt_Territory.RAMSID ,
    ARCUSFIL_SQL.Terr,
	Rpt_Territory.Terr_Name,
	Rpt_Territory.Report_Sort,
	Rpt_TerritoryAdopted.RAMSID ,
    Rpt_TerritoryAdopted.Terr_id ,
    Rpt_TerritoryAdopted.Terr_name ,
    Rpt_Territory.Report_Sort,
	PR_ID.ID_DESC,
	PD.PRD_R_BRANDNAME,
	OELINHST_SQL.Item_no 

END

----------------------------------------------------
-- insert for MTD Current RAMS
----------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) 
BEGIN

INSERT INTO #SalesSummary 

SELECT DISTINCT

		'RAMS',
		'2 - MTD Current',
--		SubString(CUST_ID.ID_DESC,3,2) as CM_SELLZONE,
		CUST_ID.IDVALUE,
		CUST_ID.ID_TITLE,
		@CurrWeekEndDate as CurrWeekEndDate, 
		@CurrToDate as CurrToDate, 
		@CurrToDateYTD as CurrToDateYTD, 
		@CurrentWeek AS CurrentWeek,
		@NumberofWeeks AS NumberofWeeks,
		@CurrToDate_Period AS CurrToDate_Period,
		@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
		SubString(CUST_ID.ID_DESC,3,2) as Terr,
		CUST_ID.ID_DESC as Terr_name,
		Rpt_Territory.Report_Sort as Report_Sort,
		left(PR_ID.ID_DESC,1) as BrandCode,
		Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
		Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
		Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
		PR_ID.ID_DESC AS BRAND,
		PD.PRD_R_BRANDNAME AS BRAND_ID,
		OD.OD_PRODUCT as Item_no,
		Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
									AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)
							   THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
							   ELSE 0 END),
		Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)									
							   THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
							   ELSE 0 END)
		
						   
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
        INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
               ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
		left outer join dbo.Rpt_Territory on
			Rpt_Territory.RAMSID = CUST_ID.IDVALUE
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT   
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
						ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
						AND Rpt_TerritoryAdopted.End_Item_no

--WHERE     OH.OH_SALE_TYPE = 'O'
--          AND (ID.ID_TITLE = N'Sale Zone')

GROUP BY    
		CUST_ID.IDVALUE,
		CUST_ID.ID_TITLE,
		SubString(CUST_ID.ID_DESC,3,2) ,
		CUST_ID.ID_DESC ,
		Rpt_Territory.Report_Sort,
		left(PR_ID.ID_DESC,1),
		Rpt_TerritoryAdopted.Terr_id,
		Rpt_TerritoryAdopted.Terr_name,
		Rpt_TerritoryAdopted.Report_Sort,
		PR_ID.ID_DESC,
		PD.PRD_R_BRANDNAME ,
		OD.OD_PRODUCT 

END

----------------------------------------------------
-- final select
----------------------------------------------------


SELECT * FROM #SalesSummary

Open in new window

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

geek_vjCommented:
Seems there is already an object by that name  (as it is a temp table) created within the same session.

Please try closing all the open query windows and try executing the code again. If it is still happening, try using - "if exists" then drop the temp table first and then proceed with creation of the temp table again

Hope this helps!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
...

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

CREATE TABLE #SalesSummary
...
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Thanks.....I am learning thanks to you. God knows we all need to do more of that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.