Code working in v6.5, fails in v7 unless compatability mode switched on

This code has been working happily for years!  When I try it in SQL Server 7
if the line a.LineCodeNo = @nLineCodeNo it gets no data but when a.LineCodeNo = 79 is hard coded the data is there!
Any ideas guys?

DROP TABLE T#TaxCapLineCode
GO

CREATE TABLE T#TaxCapLineCode
      (
      LineCodeNo            smallint,
      LineCodeOrderNo            smallint,
      LineCategoryNo            LineCategoryNoType,
      Description            varchar(80)
      )
GO

DROP TABLE T#TaxCapAmount
GO

CREATE TABLE T#TaxCapAmount
      (
      CompanyNo            CompanyNoType,
      APNo                  APNOType,
      LineCodeNo            smallint,
      LineCodeOrderNo            smallint,
      Amount                  AmountType
      )
GO

IF EXISTS (SELECT Object_Id('pr_TaxCap'))
      DROP PROCEDURE pr_TaxCap
GO

CREATE PROCEDURE pr_TaxCap
      (
      @nCompanyNo            CompanyNoType,
      @nAmountColType            smallint
      )
AS
SET NOCOUNT ON

DECLARE
      @nLineCodeNo            smallint,
      @nLineCodeOrderNo      smallint,
      @nLineCodeCategoryNo      LineCategoryNoType,
      @sLineCodeDescription      varchar(80),

      @nFetchStatusLineCode      smallint,

      @nAmountCompanyNo      CompanyNoType,
      @nAmountAPNo            APNoType,
      @nAmountLineCodeNo      smallint,
      @nAmountLineCodeOrderNo      smallint,
      @nAmountAmount            AmountType,

      @nFetchStatusAmount      smallint,

      @nAmountCol            smallint,
      @nAmountColX            smallint,
      @nAmountColY            smallint,

      @nNonZeroAmount            smallint,

      @TRUETRUE            smallint,
      @FALSEFALSE            smallint,

      @nCol                  smallint,
      @nRow                  smallint,
      @sCellValue            varchar(120)

DECLARE      cr_LineCode SCROLL CURSOR FOR
      SELECT
            LineCodeNo,
            LineCodeOrderNo,
            LineCategoryNo,
            Description
      FROM
            T#TaxCapLineCode a
      ORDER BY
            2

DECLARE      cr_Amount SCROLL CURSOR FOR
      SELECT
            CompanyNo,
            APNo,
            LineCodeNo,
            LineCodeOrderNo,
            Amount
      FROM
            T#TaxCapAmount a
      WHERE
            a.CompanyNo = @nCompanyNo
      AND      
            a.LineCodeNo = @nLineCodeNo
--            a.LineCodeNo = 79

DELETE FROM T#TaxCapLineCode

INSERT INTO T#TaxCapLineCode
      SELECT
            a.LineCodeNo,
            b.OrderNo,
            (SELECT c.LineCategoryNo FROM LineCodeCategory c WHERE a.LineCodeNo = c.LineCodeNo
            AND c.LineCategoryNo IN (0,1)),
            a.Description
      FROM
            LineCode a, LineCodeCategory b
      WHERE
            a.LineCodeNo = b.LineCodeNo
      AND
            b.LineCategoryNo = 6
      ORDER BY
            2

DELETE FROM T#TaxCapAmount

INSERT INTO T#TaxCapAmount
      SELECT
            1007,
            a.APNo,
            b.LineCodeNo,
            b.LineCodeOrderNo,
            0
      FROM
            AP a, T#TaxCapLineCode b
      WHERE
            a.CompanyNo = 1007

UPDATE T#TaxCapAmount SET
      Amount = c.Amount
FROM
      T#TaxCapAmount t,
      AP a,
      T#TaxCapLineCode b,
      LineCodeInput c
WHERE
      t.CompanyNo = c.CompanyNo
AND
      t.APNo = c.APNo
AND
      t.LineCodeNo = c.LineCodeNo
AND
      a.CompanyNo = c.CompanyNo
AND
      a.APNo = c.APNo
AND
      b.LineCodeNo = c.LineCodeNo
AND
      b.LineCategoryNo = 0
AND
      a.CompanyNo = 1007

UPDATE T#TaxCapAmount SET
      Amount = c.AmountX
FROM
      T#TaxCapAmount t,
      AP a,
      T#TaxCapLineCode b,
      LineCodeCalculate c
WHERE
      t.CompanyNo = c.CompanyNo
AND
      t.APNo = c.APNo
AND
      t.LineCodeNo = c.LineCodeNo
AND
      a.CompanyNo = c.CompanyNo
AND
      a.APNo = c.APNo
AND
      b.LineCodeNo = c.LineCodeNo
AND
      b.LineCategoryNo = 1
AND
      a.CompanyNo = 1007

SELECT
      CompanyNo,
      APNo,
      LineCodeNo,
      LineCodeOrderNo,
      Amount
FROM
      T#TaxCapAmount a
WHERE
      CompanyNo = 1007
AND
      LineCodeNo = 79

SELECT @nCompanyNo = 1007, @nLineCodeNo = 79
SELECT "@nCompanyNo = ",@nCompanyNo
SELECT "@nLineCodeNo = ",@nLineCodeNo

OPEN cr_Amount

FETCH FIRST FROM cr_Amount INTO
      @nAmountCompanyNo,
      @nAmountAPNo,
      @nAmountLineCodeNo,
      @nAmountLineCodeOrderNo,
      @nAmountAmount

SELECT @nFetchStatusAmount = @@FETCH_STATUS

select       "cr_Amount", @nAmountCompanyNo,      @nAmountAPNo, @nAmountLineCodeNo, @nAmountLineCodeOrderNo, @nAmountAmount

SELECT @nFetchStatusAmount

CLOSE cr_Amount

DEALLOCATE cr_Amount

DEALLOCATE cr_LineCode

RETURN 0

SET NOCOUNT OFF
GO

GRANT EXECUTE ON pr_TaxCap TO Development
GO
GRANT EXECUTE ON pr_TaxCap TO GroupTax
GO

EXECUTE pr_TaxCap 1007,1
GO

CompanyNo   APNo   LineCodeNo LineCodeOrderNo Amount            
----------- ------ ---------- --------------- -----------------
1007        1      79         1               172172966.00
1007        2      79         1               391191030.00
1007        3      79         1               681778625.00
1007        4      79         1               757373346.00
1007        5      79         1               798477987.00
1007        6      79         1               514067984.00
1007        7      79         1               512468651.00
1007        8      79         1               198844008.00
1007        9      79         1               123968772.00
1007        10     79         1               75812375.00
1007        11     79         1               208189020.00
1007        12     79         1               393989000.00
1007        13     79         1               1406271000.00
1007        14     79         1               947468000.00
1007        15     79         1               2367227000.00
1007        16     79         1               2122024000.00
1007        17     79         1               2153175000.00
1007        18     79         1               1222752000.00
1007        19     79         1               1071000000.00
1007        20     79         1               1200000000.00

                           
-------------- -----------
@nCompanyNo =  1007

                       
--------------- ------
@nLineCodeNo =  79

                                                             
--------- ----------- ------ ------ ------ -----------------
cr_Amount NULL        NULL   NULL   NULL   NULL

       
------
-1

LVL 4
barnesd1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tmorellCommented:
Try setting the value @nLineCodeNo before the declare of the cr_Amount cursor and see if that takes care of the issue.
0
barnesd1Author Commented:
It would appear that there is a bug with SQL Server 7.  According to MS Knowledge Base Article 217032, Variable Arguments are not refreshed after cursor is declared.  What a nuisance.  Has anyone else has this?
0
xenon_jeCommented:
The value you wanna pass to an declare cursor has to be set before this statement. So you have to do something like this:
****
declare @xx int
//some code that initializez the @xx
declare cursor ...that uses @xx
*******

if you do something like
declare cursor ...that uses @xx--it will use the value just setted before, and not the value of @xx seted before the open my_cursor.....
sorry...you have to change a little bit your code...

gl   xenon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.