sql-server - 在sql server 2005中的try catch中捕获错误时如何防止事务回滚

Question

我有一个带有记录的表 Table1

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

这里我想将 F1 Varchar 的数据类型更改为 Decimal(3,0)；
然后我创建一个具有新结构的新表。

我想通过将 varchar 显式转换为十进制来将旧表的每个记录插入到新表中。
所以我在 try catch 块中编写此命令。如果发生错误，那么在 catch 块中我可以用它的默认值填充它。
但是整个工作是在开始事务和提交事务块之间执行的。
所以当 catch 块中捕获到错误时，则无法执行进一步的工作，就会发生错误

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

即使在 try catch 块中发生错误，如何防止此事务回滚。

Answer 1

你不能。您必须始终 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.