Hey All,
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.