[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL Invalid Object Name

Posted on 2008-06-11
6
Medium Priority
?
1,550 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

591 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