CODEWITHSUNDEEP
sql-serversql-server-2005try-catchsqltransaction
Haider Ali
Haider Ali

Reputation: 2795

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

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.

Upvotes: 2

Views: 2183

Answers (2)

Jean.C
Jean.C

Reputation: 324

Unfortunately this is not possible with SQL SERVER.

Any error will doom the transaction, and a doomed transaction cannot be commited, nor unrolled to a savepoint.

The only solution is: use ANOTHER DATABASE ENGINE...

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294237

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.

Upvotes: 3

Related Questions