Errors that abort batch but not transactions cannot be caught when the CLR is on the call stack

Errors that abort batch but not transactions cannot be caught when the CLR is on the call stack by Erland Sommarskog

Active

Type:	Bug
ID:	778911
Opened:	2/10/2013 1:01:27 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

You have some T-SQL code that calls a CLR stored procedure which in its turn invokes some T-SQL code. This T-SQL causes an error that aborts the batch, without rolling back the transaction (if there is any). Currently, to my knowing, there are two such errors, of which one is the new ;THROW statement.

It turns out that this error cannot be caught, neither in the CLR code, nor in the upper-level SQL code, but the error is always passed to the client. For whatever reason, the error message has a leading newline.

One can also note that if the T-SQL code starts a transaction, the transaction survives the ordeal. The normal behaviour when the CLR exits with a different trancount than on entry that the transaction is silently rolled back (if there was no active trans on entry) or raises an error (if there was a trans prior to the call to the CLR.)

The behaviour only appears if XACT_ABORT is OFF.

Details (expand)

English

SQL Server 2012 SP1

SQL Engine

Windows 7 Professional

US English

This is the CLR procedure:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class errortest
{

[Microsoft.SqlServer.Server.SqlProcedure]
public static void uncatchable(
                 SqlString sqlcmd)
{
    // Connect through the context connection.
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        SqlCommand command = new SqlCommand();
        connection.Open();
        command.Connection = connection;
        try {
            command.CommandText = sqlcmd.ToString();
            command.ExecuteNonQuery();
        }
        catch {
            command.CommandText = "PRINT 'Entering CLR catchhandler'";
            SqlContext.Pipe.ExecuteAndSend(command);
            throw;
        }
    }
}

};

--------------------------
CREATE ASSEMBLY nocatch FROM 'C:\temp\nocatch.dll'
go
CREATE PROCEDURE uncatchable
                 @sqlcmd nvarchar(MAX)AS
EXTERNAL NAME nocatch.errortest.uncatchable
go
SET XACT_ABORT OFF
SET NOCOUNT ON
BEGIN TRY
EXEC uncatchable ';THROW 50000, ''An error is thrown'', 1'
-- EXEC uncatchable 'BEGIN TRANSACTION EXEC (''SELECT 1'') WITH RESULT SETS NONE COMMIT TRANSACTION'
END TRY
BEGIN CATCH
PRINT 'Caught error'
PRINT '<' + error_message() + '>'
END CATCH
go
SELECT @@trancount AS trancount
IF @@trancount > 0 ROLLBACK TRANSACTION

Msg 50000, Level 16, State 1, Procedure uncatchable, Line 0

An error is thrown
trancount
-----------
0

Entering CLR catchhandler
Caught error
<A .NET Framework error occurred during execution of user-defined routine or aggregate "uncatchable":
System.Data.SqlClient.SqlException: An error is thrown
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at errortest.uncatchable(SqlString sqlcmd)
.>
trancount
-----------
0

0 attachments

Hello Erland,
We looked at this issue and it doesn't meet the bar for fixing the bug in the next major release of SQL Server. SO I am resolving this bug as "won't fix". We will look at it again if we get more feedback.
Only thing I can suggest is to use XACT_ABORT ON for your session.

--
Umachandar, SQL Programmability Team