MS SQL Stored Procedure Problem

Question

I have a stored procedure that works fine on my local SQL Server (2005 or 2008 cant recall off hand) but fails when I try to create the procedure on the Production server (SQL 2000). Any help would be appreciated. TIA.

The stored procedure declaration is this:

/****** Object:  StoredProcedure [dbo].[AssignPCSCheckNumbers]    Script Date: 06/29/2009 13:12:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AssignPCSCheckNumbers]          
(          
    @MonthEnd    DATETIME,          
    @Seed        INT,          
    @ManifestKey UNIQUEIDENTIFIER,          
    @Threshold   DECIMAL(9,2)          
)          

AS          

SET NOCOUNT ON          

BEGIN          


--Create a temporary table variable to store our data          
DECLARE @MyTemp TABLE 
( 
     ProducerNumber VARCHAR(20), 
     LastCheckDate DATETIME, 
     Due DECIMAL(9,2) DEFAULT 0,
     Returned DECIMAL(9,2) DEFAULT 0          
)

--Fill the table with a listing of producers from our PCSItems table and their ACH Status
INSERT INTO @MyTemp ( ProducerNumber ) 
SELECT      PCSItems.ProducerNumber 
FROM        PCSItems
LEFT JOIN   Producer
ON          PCSItems.ProducerNumber = Producer.prodNum
WHERE       ISNULL(Producer.PayCommissionByACH,0) = 0

--UPDATE the table with the last time a check was printed for each
--of these producers
UPDATE      @MyTemp
SET         LastCheckDate = (
SELECT      ISNULL(MAX(EntryDate),'1/1/1901') 
FROM        CommissionLedger WITH (NOLOCK)
WHERE       CommissionLedger.TransactionType = 1
AND         CommissionLedger.ProducerNumber = [@MyTemp].ProducerNumber
)


--update the table with the amount of comission owed to each producer          
UPDATE      @MyTemp           
SET         Due = (
SELECT      IsNull(SUM(CommPaid),0)          
FROM        ProducerComm WITH (NOLOCK)          
WHERE       ProducerComm.CommApplies = [@MyTemp].ProducerNumber
AND         ProducerComm.EntryDate >= LastCheckDate
AND         ProducerComm.EntryDate <= @MonthEnd
)          

--update the table with the amount of commission returned by each producer                             
UPDATE      @MyTemp          
SET         Returned = (
SELECT      ISNULL(SUM(Amount), 0)
FROM        CommissionLedger WITH (NOLOCK)          
WHERE       CommissionLedger.ProducerNumber = [@MyTemp].ProducerNumber          
AND         CommissionLedger.EntryDate  >= [@MyTemp].LastCheckDate          
AND         CommissionLedger.EntryDate  <= @MonthEnd
)

--create a table to assist with our operations          
DECLARE @MyFinal TABLE 
(
    ID INT IDENTITY(1,1),           
    ProducerNumber VARCHAR(10)          
)

--just insert the producers that are owed an amount over a user specified           
--threshold          
INSERT INTO @MyFinal ( ProducerNumber )           
SELECT      ProducerNumber          
FROM        @MyTemp          
WHERE       (Due + Returned) > @Threshold           

--update our items with the check numbers finally =)          
UPDATE      PCSItems           
SET         CheckNumber = (SELECT  (([@MyFinal].ID - 1) + @Seed)      
                           FROM    @MyFinal          
                           WHERE   [@MyFinal].ProducerNumber = PCSItems.ProducerNumber)          

SET NOCOUNT OFF          

END
GO

And the error the server responds with is this:

Msg 107, Level 16, State 2, Procedure AssignPCSCheckNumbers, Line 35
The column prefix '@MyTemp' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure AssignPCSCheckNumbers, Line 45
The column prefix '@MyTemp' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure AssignPCSCheckNumbers, Line 55
The column prefix '@MyTemp' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure AssignPCSCheckNumbers, Line 55
The column prefix '@MyTemp' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure AssignPCSCheckNumbers, Line 79
The column prefix '@MyFinal' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure AssignPCSCheckNumbers, Line 79
The column prefix '@MyFinal' does not match with a table name or alias name used in the query.

Answer 1

That should be created no problem on a 2000 box (and I verified by creating it on my sql 2000 box). Are you sure your database is not in 7.0 compatibility mode?

run

sp_helpdb 'YourDatabaseName'

and look if compatability is 80