Solved

SQL Invalid Object Name

Posted on 2008-06-11
6
1,480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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