Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Invalid Object Name

Posted on 2008-06-11
6
Medium Priority
?
1,533 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 2000 total points
ID: 21760065
wow...can't believe I missed it....you have to remove the GO statement from your procedure.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.
Suggested Courses

810 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