Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Invalid Object Name

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

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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 article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

609 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