Solved

SQL Invalid Object Name

Posted on 2008-06-11
6
1,425 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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now