I recently ran into an SQL error due to the following statement in our legacy code. He tries to reset the temporary table and move on if it is not defined. Clearly this is a bad way to check for the existence of a temporary table, but that is not my question here.
BEGIN TRY
DROP TABLE
END TRY
BEGIN CATCH
END CATCH
The operator works fine (without any error) as it is, but as soon as you put it in the Begin Tran / Commit Tran block, as shown below, the behavior becomes interesting.
BEGIN TRAN
BEGIN TRY
DROP 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() 1 END CATCH. , DROP TABLE, .
BEGIN TRAN
BEGIN TRY
DROP TABLE
END TRY
BEGIN CATCH
END CATCH
PRINT XACT_STATE()
COMMIT TRAN
, XACT_ABORT . , . 2008 R2 2012, / , Try... Catch block and Transaction .
1. script 2008 R2 2012. INSERT dbo.UserOptionsLog Begin Tran Begin Try Begin Catch , .
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
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)