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_CURI think you want to use @.@.fetch_status in SQL Server instead of @.@.sqlstatus|||and DEALLOCATE CURSOR SUSPENSE_AMOUNT_CUR should be DEALLOCATE SUSPENSE_AMOUNT_CUR.

this does not have to be a cursor.|||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|||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?|||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|||I beleive this will eliminate all of your code

Post the DDL so I can test it

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|||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|||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|||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"?|||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|||If @.suspense_amount>0
begin Tran
Delete From #test3 Where Blei_ck=@.suspense_blei_ck
Commit Tran
End|||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.