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

AFAIK tables in where clause can't referenced by name and you should their alias for this purpose, in MSSQL2000 book online for search condition 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, there is no table or alias and it is only alias

Answer 2

It's been a while but I seem to remember SQL 2000 requiring you to use an alias when you are referencing the table in both the update statement and in a subquery. Hope this helps or you find the solution.

Answer 3

It's not the @ notation that is causing the problem, it's the brackets @a not [@a].

Answer 4

I was not aware that 2000 supported table variables, as I suspected in my first answer.

Now I tried in Query Analyzer and found that @table is handled differently from [@table] which results in an error message "invalid object name @table".

I suggest to remove the square brackets from the @ table names.

update:

This page indicates using a table alias might fix the problem. I just experimented with:

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

which failed with an error. Rewriting as

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

works. Hope it works for you too ;)

Answer 5

This is loosely quoted from a question I asked awhile ago (Link) so if it works for you, upvote Mike L's response instead of mine.

If you use the Database Publishing Wizard to create scripts for your SPs, you can build them in 2005 and use it to deploy to 2000 letting the wizard take care of any compatability issues you may have.

Answer 6

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