Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35

    Unanswered: Cursor Question (dhl)

    Moving code that is running on Sybase to SQL Sever 2005. Code uses a curser. When I try running the code on the SQL server I keep getting the following errors. Here is the error message and code. Thank you for your help. David

    Msg 137, Level 15, State 2, Line 356
    Must declare the scalar variable "@@sqlstatus".
    Msg 156, Level 15, State 1, Line 389
    Incorrect syntax near the keyword 'CURSOR'.

    DECLARE SUSPENSE_AMOUNT_CUR CURSOR FOR
    SELECT
    ENDDATE,
    SBSB_ID,
    PRINTDB_DUE,
    SSA_DUE,
    ACH_DUE,
    OTHER_DUE,
    GRGR_ID,
    SGSG_ID,
    SBSB_NAME,
    MEME_HICN,
    MEME_CK,
    GRGR_CK,
    SBSB_CK,
    BLEI_CK

    FROM #TEST3 WHERE (PRINTDB_DUE + SSA_DUE + ACH_DUE)=0

    OPEN SUSPENSE_AMOUNT_CUR
    FETCH SUSPENSE_AMOUNT_CUR INTO
    @SUSPENSE_ENDDATE,
    @SUSPENSE_SBSB_ID,
    @SUSPENSE_PRINTDB_DUE,
    @SUSPENSE_SSA_DUE,
    @SUSPENSE_ACH_DUE,
    @SUSPENSE_OTHER_DUE,
    @SUSPENSE_GRGR_ID,
    @SUSPENSE_SGSG_ID,
    @SUSPENSE_SBSB_NAME,
    @SUSPENSE_MEME_HICN,
    @SUSPENSE_MEME_CK,
    @SUSPENSE_GRGR_CK,
    @SUSPENSE_SBSB_CK,
    @SUSPENSE_BLEI_CK

    WHILE @@sqlstatus = 0
    BEGIN

    select @SUSPENSE_AMOUNT=Sum(BLAC.BLAC_CREDIT_AMT)-sum(BLAC.BLAC_DEBIT_AMT)
    from fauafpr0_pids.dbo.CMC_BLAC_BILL_ACCT AS BLAC
    where BLAC.BLEI_CK=@SUSPENSE_BLEI_CK
    AND BLAC.ACGL_TYPE='S'AND BLAC.ACGL_ACTIVITY='A'
    AND BLAC.BLAC_POSTING_DT BETWEEN '01/01/2005' AND @ENDDATE

    BEGIN
    IF @SUSPENSE_AMOUNT>0
    DELETE FROM #TEST3 WHERE BLEI_CK=@SUSPENSE_BLEI_CK
    COMMIT TRAN
    END

    FETCH SUSPENSE_AMOUNT_CUR INTO
    @SUSPENSE_ENDDATE,
    @SUSPENSE_SBSB_ID,
    @SUSPENSE_PRINTDB_DUE,
    @SUSPENSE_SSA_DUE,
    @SUSPENSE_ACH_DUE,
    @SUSPENSE_OTHER_DUE,
    @SUSPENSE_GRGR_ID,
    @SUSPENSE_SGSG_ID,
    @SUSPENSE_SBSB_NAME,
    @SUSPENSE_MEME_HICN,
    @SUSPENSE_MEME_CK,
    @SUSPENSE_GRGR_CK,
    @SUSPENSE_SBSB_CK,
    @SUSPENSE_BLEI_CK

    END
    CLOSE SUSPENSE_AMOUNT_CUR
    DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think you want to use @@fetch_status in SQL Server instead of @@sqlstatus
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    and DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR should be DEALLOCATE SUSPENSE_AMOUNT_CUR.

    this does not have to be a cursor.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    Thanks, I took out the "CURSOR" and changed @@sqlstatus = 0 to @@FETCH_STATUS = 0. Now I getting this error:

    Msg 3902, Level 16, State 1, Line 368
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

    Thanks for your help. First time working with cursor's. david

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dlamontagne
    Thanks, I took out the "CURSOR" and changed @@sqlstatus = 0 to @@FETCH_STATUS = 0. Now I getting this error:

    Msg 3902, Level 16, State 1, Line 368
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

    Thanks for your help. First time working with cursor's. david
    Well that error is pretty specific...where's your BEGIN TRAN?

    Also, you should convert this to a set based process

    Got DDL?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    The Code had two begin but they just say begin not BEGIN TRAN. Not sure how to convert to a based process or what is DDL. Never worked with cursor. This code is running on a Sybase system and I need to move it to SQL Server 2005. Thanks you for your help. david

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I beleive this will eliminate all of your code

    Post the DDL so I can test it

    Code:
    DELETE t 
      FROM #TEST3 t
      JOIN fauafpr0_pids.dbo.CMC_BLAC_BILL_ACCT AS BLAC
        ON t.BLEI_CK = BLAC.BLEI_CK
     WHERE BLAC.ACGL_TYPE='S'
       AND BLAC.ACGL_ACTIVITY='A'
       AND BLAC.BLAC_POSTING_DT BETWEEN '01/01/2005' 
    HAVING SUM(BLAC.BLAC_CREDIT_AMT)-SUM(BLAC.BLAC_DEBIT_AMT) > 0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by dlamontagne
    The Code had two begin but they just say begin not BEGIN TRAN. Not sure how to convert to a based process or what is DDL. Never worked with cursor. This code is running on a Sybase system and I need to move it to SQL Server 2005. Thanks you for your help. david

    Read the hint sticky at the top of the forum to see what you need to post
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    This is one small part of a larger procedure. The other parts that use the cursor are having the same problem. I posted the above code, it was the smallest number of lines. Still need to find out why I am getting a the same error on the other code that uses the cursor. Msg 3902, Level 16, State 1, Line 368
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
    David

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by dlamontagne
    Thanks, I took out the "CURSOR" and changed @@sqlstatus = 0 to @@FETCH_STATUS = 0. Now I getting this error:

    Msg 3902, Level 16, State 1, Line 368
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
    What do you mean by "I took out the cursor"?
    Inspiration Through Fermentation

  11. #11
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    In the code DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR
    after I removed the word CURSOR in the line of code the error that fixed the error: Incorrect syntax near the keyword 'CURSOR'.
    Now I getting this error: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. David

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If @suspense_amount>0
    begin Tran
    Delete From #test3 Where Blei_ck=@suspense_blei_ck
    Commit Tran
    End
    Inspiration Through Fermentation

  13. #13
    Join Date
    Jul 2007
    Location
    Orlando,FL
    Posts
    35
    When I change my code to "BEGIN TRAN" and add in the If code, I now get this error: Incorrect syntax near the keyword 'CLOSE'.
    David
    P.S. Is there a good place on the web that I can find good documations on how to use Cursor's.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •