how to prevent a transaction to rollback when an error caught in try catch in sql server 2005

Question

I have a table Table1 with records

Table1
-------------------------------
ID  F1
-------------------------------
01  1
02  OK
03  52
04  53
05  Null
------------------------------

here I want to change data type of F1 Varchar to Decimal(3,0); then i create a new table with new structure.

I want to insert each recod of old table in new, by explicitly converting varchar to decimal. so i am writing this command in try catch block. where if error generates, then in catch block i can fill it with its default value. but this whole working is performed between begin transaction and commit transaction block. So when error caught in catch block, then no furthure working can be performed, error occurs

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

How to prevent this transaction to be rolled back even when error occures in try catch block.

If the errors are straightforward enough, could you fix your code to ensure those errors don't occur? If this is for some one-off datafix you might be able to just account for all possible errors and once these have been taken care of you shouldn't need to worry about it bailing and trying to rollback. — Darth Continent

Remus Rusanu Remus Rusanu · Accepted Answer · 2012-12-27T14:25:47

You cannot. You must always check the XACT_STATE and act accordingly:

Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction. When a batch finishes, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This indicates that an uncommittable transaction was detected and rolled back.

how to prevent a transaction to rollback when an error caught in try catch in sql server 2005

2 Answers