Problem with MS SQL stored procedure

Question

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

A stored procedure declaration is as follows:

/****** 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 is responding to 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

This should be created without problems in window 2000 (and I checked it by creating it in my sql 2000 block). Are you sure your database is not in compatibility mode with 7.0?

run

sp_helpdb 'YourDatabaseName'

and look, compatibility is 80

Answer 2

, 2000 , .

Query Analyzer , @table , [@table], " @table".

@.

:

, . :

UPDATE @a SET a = a + b FROM @a INNER JOIN @b ON @a.a = @b.b

.

UPDATE @a SET a = a + b FROM @a aa INNER JOIN @b bb ON aa.a = bb.b

. , ;)

Answer 3

, (), , , . .

SP, 2005 2000 , .

Answer 4

@, , @a not [@a].

Answer 5

, , , , SQL 2000 , , , . , .

Answer 6

AFAIK tables, in which the offer cannot be referenced by name, and you must specify their alias for this purpose, in MSSQL2000 book online for the search term, we see:

<search_condition>:=
  { constant 
    | scalar_function 
    | [ alias. ] column 
    | local_variable 
    | ( expression ) 
    | ( scalar_subquery ) 
    | { unary_operator } expression 
    | expression { binary_operator } expression 
  }

and, as you see, no table or alias, and that's justalias