sql-server - MS SQL 存储过程问题

Question

我有一个存储过程在我的本地 SQL Server 上运行良好(2005 或 2008 无法立即记忆起来)但是当我尝试在生产服务器(SQL 2000)上创建过程时失败。任何帮助，将不胜感激。 TIA。

存储过程声明是这样的:

/****** 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

服务器响应的错误是这样的:

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

这应该在 2000 机器上创建没有问题(我通过在我的 sql 2000 机器上创建它来验证)。您确定您的数据库未处于 7.0 兼容模式吗？

运行

sp_helpdb '你的数据库名称'

看看兼容性是不是80