March 5, 2013 at 10:19 am
We have defined a cursor and a transaction which empowers to process data record by record. The desired behavior is If there is a failure in an iteration then it should rollback transaction, log error in a custom table and proceed for next record in the queue, finally the stored procedure should indicate execution as successful.
As per mentioned logic, in SQL Server 2008 it performs same as of above explained requirements and completes its execution whereas in SQL Server 2000 it also completes execution but prompts error message at the end of stored procedure execution if there was an error during execution of an iteration.
Our external application fires a stored procedure in SQL Server 2000 which contain similar logic and gets fail if there was an error in an iteration. We want to achieve same behavior in SQL Server 2000 as of SQL Server 2008’s behavior.
SQL Server 2000 T-SQL Code:
DECLARE @Flag AS INT
DECLARE @errorcode AS INT
DECLARE CSR_TEST CURSOR
FOR
SELECT 1 AS Flag
UNION
SELECT 2 AS Flag
OPEN CSR_TEST
FETCH NEXT FROM CSR_TEST INTO @Flag
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
IF @Flag = 1
BEGIN
SELECT 1 / 0
SELECT @errorcode = @@Error
IF @errorcode <> 0
GOTO LogError
END
IF @Flag = 2
BEGIN
SELECT 1 / 1
SELECT @errorcode = @@Error
IF @errorcode <> 0
GOTO LogError
END
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
GOTO ProcEnd
LogError:
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTIOM'
--Error Logging in Custom Table
ProcEnd:
FETCH NEXT FROM CSR_TEST INTO @Flag
END
CLOSE CSR_TEST
DEALLOCATE CSR_TEST
SQL Server 2008 T-SQL Code:
DECLARE @Flag AS INT
DECLARE @errorcode AS INT
DECLARE CSR_TEST CURSOR
FOR
SELECT 1 AS Flag
UNION
SELECT 2 AS Flag
OPEN CSR_TEST
FETCH NEXT FROM CSR_TEST INTO @Flag
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF @Flag = 1
BEGIN
SELECT 1 / 0
SELECT @errorcode = @@Error
IF @errorcode <> 0
RAISERROR (8134,0,0)
END
IF @Flag = 2
BEGIN
SELECT 1 / 1
SELECT @errorcode = @@Error
IF @errorcode <> 0
RAISERROR (8134,0,0)
END
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTIOM'
--Error Logging in Custom Table
END CATCH
FETCH NEXT FROM CSR_TEST INTO @Flag
END
CLOSE CSR_TEST
DEALLOCATE CSR_TEST
March 5, 2013 at 11:12 am
I'll venture a guess.
SQL 2000 isn't handling an exception. An error happens and it really doesn't care that you "handled" it.
So in error handling part for SQL 2000, set @@error to zero
http://msdn.microsoft.com/en-us/library/aa933181(v=sql.80).aspx
Bob
SuccessWare Software
March 5, 2013 at 12:32 pm
From what you are describing the best approach would be to get rid of the cursor entirely. There are nowhere near enough details in your post to offer much detailed help but from your description this sounds like a single set based operation to me. No need to process RBAR (row by agonizing row).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply