最近，由于遗留代码中的以下语句，我遇到了SQL错误。它试图删除一个临时表，如果尚未定义它，则继续前进。显然，这是检查临时表是否存在的不好方法，但这不是我的问题。
BEGIN TRY
DROP TABLE #my_temp_table
END TRY
BEGIN CATCH
END CATCH
该语句按原样运行正常（没有任何错误），但是将其放入如下所示的Begin Tran / Commit Tran块中后，该行为就变得很有趣。
BEGIN TRAN
BEGIN TRY
DROP TABLE #my_temp_table
END TRY
BEGIN CATCH
END CATCH
COMMIT TRAN
我的理解是Try..Catch块不会影响事务-一旦进入Catch块，事务将处于不可提交状态，并且事务将回滚，这就是我在SQL Server 2008 R2上看到的内容（SP1）-10.50.2550.0。当它在Begin Tran / Commit Tran块中执行时，我们将得到一个错误：
Msg 3930, Level 16, State 1, Line 8
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
但是，它在SQL Server 2012-11.0.5058.0上运行时没有任何错误。 XACT_STATE（）在END CATCH行之后返回1。事务将被提交，并且如果DROP TABLE语句前后有其他数据更改，则更改将保留。
BEGIN TRAN
BEGIN TRY
DROP TABLE #my_temp_table
END TRY
BEGIN CATCH
END CATCH
PRINT XACT_STATE()
COMMIT TRAN
在所有这些测试中，我确保XACT_ABORT为OFF。所以我的问题是什么将导致这种行为差异。这是2008 R2和2012之间的真正区别，还是控制Try ... Catch块和事务工作方式的一些服务器/数据库设置。
编辑1：我尝试在2008 R2和2012实例上运行以下脚本。我还尝试将INSERT dbo.UserOptionsLog行放在不同的位置，在开始Tran之前，在开始尝试之后，在开始Catch之后，在提交tran之后，但是它不会改变结果。
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'UserOptionsLog')
CREATE TABLE dbo.UserOptionsLog([Set Option] SYSNAME, [Value] VARCHAR(100), ID INT IDENTITY NOT NULL)
BEGIN TRAN
DELETE FROM dbo.UserOptionsLog
INSERT dbo.UserOptionsLog EXEC('DBCC USEROPTIONS')
SELECT * FROM dbo.UserOptionsLog
BEGIN TRY
DROP TABLE #my_temp_table
END TRY
BEGIN CATCH
END CATCH
COMMIT TRAN
2008 R2实例的结果。
Set Option Value ID
textsize 2147483647 40
language us_english 41
dateformat mdy 42
datefirst 7 43
lock_timeout -1 44
quoted_identifier SET 45
arithabort SET 46
ansi_null_dflt_on SET 47
ansi_warnings SET 48
ansi_padding SET 49
ansi_nulls SET 50
concat_null_yields_null SET 51
isolation level read committed 52
来自2008 R2实例的消息
(0 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(13 row(s) affected)
(13 row(s) affected)
Msg 3930, Level 16, State 1, Line 18
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
2012年实例的结果。
Set Option Value ID
textsize 2147483647 53
language us_english 54
dateformat mdy 55
datefirst 7 56
lock_timeout -1 57
quoted_identifier SET 58
arithabort SET 59
ansi_null_dflt_on SET 60
ansi_warnings SET 61
ansi_padding SET 62
ansi_nulls SET 63
concat_null_yields_null SET 64
isolation level read committed 65
来自2012年实例的消息。
(13 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(13 row(s) affected)
(13 row(s) affected)
最佳答案
这就是我在SQL Server 2008 R2（SP1）-10.50.2550.0上看到的。
当它在Begin Tran / Commit Tran块中执行时，我们将得到
一个错误：
消息3930，级别16，状态1，第8行当前交易不能
已提交，并且不支持写入日志文件的操作。
回滚事务。消息3998，第16层，状态1，第1行
在批处理结束时检测到不可提交的事务。的
事务被回滚。但是，它在SQL上运行没有任何错误
Server 2012-11.0.5058.0。 [...]
我相信造成这种差异的原因。行为是XACT_ABORT设置的值。
当OFF然后XACT_STATE()返回1时，TX是可提交的，并且COMMIT TRAN在执行时没有错误：
SET XACT_ABORT OFF
BEGIN TRAN
BEGIN TRY
DROP TABLE #my_temp_table
END TRY
BEGIN CATCH
END CATCH
PRINT XACT_STATE()
COMMIT TRAN
但是什么时候是
ONSET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
DROP TABLE #my_temp_table
END TRY
BEGIN CATCH
END CATCH
PRINT XACT_STATE()
COMMIT TRAN
由于
CATCH块拦截的错误/异常，TX变为半球形/不可提交（-1），并且COMMIT TRAN引发了另一个错误/异常：-1
Msg 3930, Level 16, State 1, Line 10
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
更新：
我转载了SQL2008R2上报告的行为。在2008R2上，似乎无论XACT_ABORT（ON / OFF）具有什么值，TX都是不可使用的。 SQL2012因此更改了此行为：仅当XACT_ABORT为ON时，TX才不可使用。