SQL 存储过程错误：当前事务无法提交且无法支持写入日志文件的操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13950402/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error in stored procedure : current transaction cannot be commited and cannot support operations that write to the log file
提问by hermann
The error message is:
错误信息是：
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
当前事务无法提交，不支持写入日志文件的操作。回滚事务。
This part right here causes an error (once I comment the SELECT clause out everything runs smoothly).
此处的这部分会导致错误（一旦我将 SELECT 子句注释掉，一切都会顺利运行）。
DECLARE @TSV_Target_Counter INT
DECLARE @TargetTable nvarchar(255)
DECLARE @TargetColumn nvarchar(255)
DECLARE @Value nvarchar(4000)
DECLARE @SQLSTR nvarchar(4000)
SET @TSV_Target_Counter = ( SELECT MIN(Transition_Set_Variable_ID)
FROM @TSV_WithTarget )
SET @TargetTable = ( SELECT TargetTable
FROM @TSV_WithTarget
WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
SET @TargetColumn = ( SELECT TargetColumn
FROM @TSV_WithTarget
WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
SET @Value = ( SELECT Value
FROM @TSV_WithTarget
WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
-- problem starts here
SELECT @SQLSTR = 'UPDATE Business_Partner AS BP
INNER JOIN BP_Contact AS BPC ON BP.Business_Partner_ID = BPC.Business_Partner_ID
INNER JOIN Due_Diligence AS DD ON BPC.BP_Contact_ID = DD.BP_Contact_ID
SET' + @TargetColumn + ' = ' + @Value + '
WHERE DD.Process_Instance_ID = ' + @Process_Instance_ID
-- ends here
EXEC(@SQLSTR);
Am I doing something wrong? I am trying to test this SP with this transaction :
难道我做错了什么？我正在尝试使用此交易测试此 SP：
BEGIN TRANSACTION T1
EXEC Process_Instance_Value_AddAlter -- the name of the SP
REVERT
ROLLBACK TRANSACTION T1
回答by Remus Rusanu
You are operating in the context of an uncommitable (aka. 'doomed') transaction. Which implies there is more code that you did not show and probably the call occurs from a CATCH block. See Uncommittable Transactions and XACT_STATE:
您是在不可提交（又名“注定”）事务的上下文中进行操作。这意味着您没有显示更多代码，并且调用可能来自 CATCH 块。请参阅不可提交的事务和 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.
如果在 TRY 块中产生的错误导致当前交易的状态无效，则该交易被归类为不可提交的交易。当错误发生在 TRY 块内时，通常会在 TRY 块外结束事务的错误会导致事务进入不可提交状态。不可提交的事务只能执行读操作或 ROLLBACK TRANSACTION。事务不能执行任何会生成写操作或 COMMIT TRANSACTION 的 Transact-SQL 语句。如果事务已被归类为不可提交事务，则 XACT_STATE 函数返回值 -1。批处理完成后，数据库引擎会回滚所有活动的不可提交事务。如果在事务进入不可提交状态时没有发送错误消息，当批处理完成时，错误消息将发送到客户端应用程序。这表明检测到一个不可提交的事务并回滚。
The fix is quite simple: do not call the procedure from an uncommitable transaction context. Always check the XACT_STATE()in a CATCH block.
修复方法很简单：不要从不可提交的事务上下文中调用过程。始终检查XACT_STATE()CATCH 块中的。
