Solved

SQL Invalid Object Name

Posted on 2008-06-11
6
1,422 Views
Last Modified: 2012-05-05
I am trying to save the following code as a stored procedure but when I try to save it I get a "server: msg 208, Level 16, State 1, Line 2--Invalid object name '#GP_Projections'.
Help me!!!!
Create Procedure ROM_REPORT_TEST_3

as
 

Create Table #Sales_Projections(Customer varchar(200), Vehicle varchar(200), 

RONumber varchar(300), RepairPackageID varchar(300), PrimaryContactID varchar(300), 

VehicleID varchar(300), RepairStageCode varchar(300), RepairStageCodeDescr varchar(300),

CreatedDate datetime, Stamp int, Team varchar(300), AuthorizationContactID int, 

ClaimNumber varchar(300), EstimatorContactID varchar(200), TeamCode varchar(300),

InsuranceCompany varchar(300), Estimator varchar(300), ArrivedDate datetime,

DeliveredDate datetime, ScheduledDeliveryDate datetime, TechID varchar(300),

AssignedTechnician varchar(100), ProdTempID varchar(300), ProdStage varchar(300),

TotalSale money, TotalSaleChar varchar(300), Hr1 int, Hr2 int, Hr3 int, RPActionCode varchar(300),

PartStatus int, SubStatus int, Followupflag varchar(300), JobNumber int, HOT int, intschdelDate varchar(300), 

businessunitid varchar(300), storecode varchar(300) )
 
 

Declare	@BusinessUnitID2 nvarchar(500)

Declare	@GPDATA nvarchar(500)

Declare @Statement_Sales as nvarchar(500)

Declare @Statement_Gross  as nvarchar(500)

Declare @StartingDate2 as nvarchar(500)

Declare @EndingDate2 as nvarchar(500)

Declare @Status as nvarchar(500)

Declare Sales_Cursor CURSOR FOR
 
 

SELECT     [ID]

FROM         westwood.dbo.BusinessUnit

WHERE     (StoreCode <> 'CS1')
 

OPEN Sales_Cursor

--Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement. 
 

FETCH NEXT FROM Sales_Cursor

INTO 	@BusinessUnitID2
 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

set @status='ARR'

Set @Statement_Sales='westwood.dbo.AA_SelectFileRackExtended_John @BusinessUnitID='+@BusinessUnitID2+', @DeliveryWindow =14, @DeliveryDateID = 0, @TechID = 0, @ProdTempID = 0, @RepairStage =' + @status + ', @InsuranceCompanyFlag = 1, @TeamCodeFlag = 1, @EstimatorFlag = 1'

INSERT INTO #Sales_Projections

EXEC(@Statement_Sales)
 

set @status='IPR'

Set @Statement_Sales='westwood.dbo.AA_SelectFileRackExtended_John @BusinessUnitID='+@BusinessUnitID2+', @DeliveryWindow =14, @DeliveryDateID = 0, @TechID = 0, @ProdTempID = 0, @RepairStage =' + @status + ', @InsuranceCompanyFlag = 1, @TeamCodeFlag = 1, @EstimatorFlag = 1'

INSERT INTO #Sales_Projections

EXEC(@Statement_Sales)
 

set @status='RDY'

Set @Statement_Sales='westwood.dbo.AA_SelectFileRackExtended_John @BusinessUnitID='+@BusinessUnitID2+', @DeliveryWindow =14, @DeliveryDateID = 0, @TechID = 0, @ProdTempID = 0, @RepairStage ='+ @status +', @InsuranceCompanyFlag = 1, @TeamCodeFlag = 1, @EstimatorFlag = 1'

INSERT INTO #Sales_Projections

EXEC(@Statement_Sales)
 

set @status='DEL'

Set @Statement_Sales='westwood.dbo.AA_SelectFileRackExtended_John @BusinessUnitID='+@BusinessUnitID2+', @DeliveryWindow =14, @DeliveryDateID = 0, @TechID = 0, @ProdTempID = 0, @RepairStage ='+ @status +', @InsuranceCompanyFlag = 1, @TeamCodeFlag = 1, @EstimatorFlag = 1'

INSERT INTO #Sales_Projections

EXEC(@Statement_Sales)
 

--UPDATE #Sales_Projections

--SET businessunit = @BusinessUnitID2

--WHERE     (businessunit is null)
 

FETCH NEXT FROM Sales_Cursor

   INTO	@BusinessUnitID2

END
 

CLOSE Sales_Cursor

DEALLOCATE Sales_Cursor
 

SELECT     #Sales_Projections.*, Westwood.dbo.v_billed_Jobs.RepairPackageID AS Billed

INTO #SALES_PROJECTIONS_FINAL

FROM         #Sales_Projections LEFT OUTER JOIN

                      westwood.dbo.v_billed_Jobs ON #Sales_Projections.RepairPackageID = westwood.dbo.v_billed_Jobs.RepairPackageID AND 

                      #Sales_Projections.businessunitid = westwood.dbo.v_billed_Jobs.BusinessUnitID

WHERE     (westwood.dbo.v_billed_Jobs.RepairPackageID IS NULL)
 
 
 

Create Table #GP_Projections(SubDepartmentDescr varchar(200), SubDepartmentPrintSequence varchar(200), 

SaleHours int, SaleDollars money, Costhours int, costdollars money, GPtarget int,

Acthours int, actdollars int, ProjHours int, ProjDollars int, GP int, DepartmentType varchar(200),

DepartmentGroupCode  varchar(100), GroupDescription varchar(100), ROID varchar(100)  )

Declare GP_Cursor CURSOR FOR
 
 

SELECT     [RepairPackageID]

FROM         #SALES_PROJECTIONS_FINAL
 
 

OPEN GP_Cursor

--Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement. 
 

FETCH NEXT FROM GP_Cursor

INTO 	@GPDATA
 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN
 

Set @Statement_Gross='westwood.dbo.AA_SelectRPJobCost_JOHN @RepairPackageID='+ @GPDATA

INSERT INTO dbo.#GP_Projections

EXEC(@Statement_Gross)
 

--UPDATE #Sales_Projections

--SET businessunit = @BusinessUnitID2

--WHERE     (businessunit is null)
 

FETCH NEXT FROM GP_Cursor

   INTO	@GPDATA

END
 

CLOSE GP_Cursor

DEALLOCATE GP_Cursor

GO
 

Select ROID, AVG(GP) as GP_Percent

INTO #GP_JOBS

FROM dbo.#GP_Projections

GROUP BY ROID
 

SELECT RepairPackageID, #SALES_PROJECTIONS_FINAL.ScheduledDeliveryDate, 

#SALES_PROJECTIONS_FINAL.TotalSale, GP_Percent,#SALES_PROJECTIONS_FINAL.Storecode

INTO #Westwood_Forecast

FROM         #SALES_PROJECTIONS_FINAL LEFT OUTER JOIN

                      #GP_JOBS ON #SALES_PROJECTIONS_FINAL.RepairPackageID = #GP_JOBS.ROID
 

--SELECT * FROM #Westwood_Forecast
 

--Month END

SELECT     Storecode as ROLocationCode, SUM(TotalSale) as CR_Month

FROM        #Westwood_Forecast

WHERE    (ScheduledDeliveryDate BETWEEN '6/1/08' AND '6/30/08') 

GROUP BY Storecode
 
 

--Next Month

SELECT     Storecode as ROLocationCode, SUM(TotalSale) as CR_Month

FROM        #Westwood_Forecast

WHERE    (ScheduledDeliveryDate BETWEEN '7/1/08' AND '7/30/08') 

GROUP BY Storecode
 
 

--After next month

SELECT     Storecode as ROLocationCode, SUM(TotalSale) as CR_Month

FROM        #Westwood_Forecast

WHERE    (ScheduledDeliveryDate BETWEEN '8/1/08' AND '6/30/12') 

GROUP BY Storecode
 
 

--WIP SALES

SELECT     Storecode as ROLocationCode, SUM(TotalSale) as CR_Month

FROM        #Westwood_Forecast

WHERE    (ScheduledDeliveryDate BETWEEN '6/1/08' AND '6/30/12') 

GROUP BY Storecode
 

--WIP GP

SELECT     Storecode as ROLocationCode, AVG(GP_Percent) as CR_Month

FROM        #Westwood_Forecast

WHERE    (ScheduledDeliveryDate BETWEEN '6/1/08' AND '6/30/12') 

GROUP BY Storecode
 
 
 
 

DROP TABLE #SALES_PROJECTIONS_FINAL

DROP TABLE #GP_Projections

DROP TABLE #GP_JOBS

DROP TABLE #Westwood_Forecast

DROP Table #Sales_Projections

Open in new window

0
Comment
Question by:fdorazio
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21759968
remove the dbo. before your temp table

INSERT INTO dbo.#GP_Projections
 
should be

INSERT INTO #GP_Projections
0
 

Author Comment

by:fdorazio
ID: 21760030
Sorry that was a typo. I removed the 'dbo' s and still the same error.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21760065
wow...can't believe I missed it....you have to remove the GO statement from your procedure.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 31

Expert Comment

by:James Murrell
ID: 21760114
thats the go @ line 119 well spotted chapmandew
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21760126
Thank you.

The GO statement isn't really a TSQL statement at all.  It is something that indicates the ending of a batch of statements, which means that your proc ends there, and everything else after that doesn't have access to what was inside your procedure.  Just remove it and you should be fine.
0
 

Author Closing Comment

by:fdorazio
ID: 31466150
I can't believe I missed that.....Thanks.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

13 Experts available now in Live!

Get 1:1 Help Now