MS SQL Stored Procedure Problem

MS SQL Stored Procedure Problem

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 by SQLMenace for MS SQL Stored Procedure Problem

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

Answer by Austin Salonen for MS SQL Stored Procedure Problem

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 by devio for MS SQL Stored Procedure Problem

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 by JeffO for MS SQL Stored Procedure Problem

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

Answer by Jeff Siver for MS SQL Stored Procedure Problem

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 by BigBoss for MS SQL Stored Procedure Problem

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:

:=    { 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

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72