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
Solved

SQL Invalid Object Name

Posted on 2008-06-11
6
1,445 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

789 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