有一个大规模升级到错误的SQL Server 2005中处理这些文章是相当广泛： 在SQL
2005和后来的厄兰Sommarskog错误处理和错误处理SQL2000鈥由厄兰Sommarskog后台

最好的方法是这样的：

创建您的存储过程，如下所示：

CREATE PROCEDURE YourProcedure
AS
BEGIN TRY
    BEGIN TRANSACTION --SqlTransaction
    DECLARE @ReturnValue int
    SET @ReturnValue=NULL

    IF (DAY(GETDATE())=1 --logical error
    BEGIN
        SET @ReturnValue=5
        RAISERROR('Error, first day of the month!',16,1) --send control to the BEGIN CATCH block
    END

    SELECT 1/0  --actual hard error

    COMMIT TRANSACTION --SqlTransaction
    RETURN 0

END TRY
BEGIN CATCH
    IF XACT_STATE()!=0
    BEGIN
        ROLLBACK TRANSACTION --only rollback if a transaction is in progress
    END

    --will echo back the complete original error message to the caller
    --comment out if not needed
    DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int

    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)

    RETURN ISNULL(@ReturnValue,1)

END CATCH

GO

但是，仅适用于SQL Server 2005及更高版本。如果不使用SQL Server 2005中的TRY-CATCH块，则很难删除SQL
Server发送回的所有消息。在extra messages你指的是通过回滚如何使用@@ TRANCOUNT处理的性质造成的：

来自http://www.sommarskog.se/error-
handling-I.html#trancount

@@ trancount是一个全局变量，它反映嵌套事务的级别。每个BEGIN TRANSACTION将@@
trancount增加1，并且每个COMMIT TRANSACTION将@@ trancount减少1。直到@@
trancount达到0才真正提交任何内容。ROLLBACK TRANSACTION将所有内容回滚到最外面的BEGIN
TRANSACTION（除非您已经使用了相当奇特的保存交易），并将@@ trancount强制为0，以先前值为准。

当您退出存储过程时，如果@@ trancount的值与该过程开始执行时的值不同，则SQL
Server会引发错误266。但是，如果从触发器中直接调用了该过程，则不会引发此错误。或间接地。如果在SET IMPLICIT TRANSACTIONS
ON上运行，则不会引发任何异常

如果您不想收到有关事务计数不匹配的警告，则您一次只能打开一个事务。您可以通过创建以下所有过程来做到这一点：

CREATE PROC YourProcedure
AS
DECLARE @SelfTransaction char(1)
SET @SelfTransaction='N'

IF @@trancount=0
BEGIN
    SET @SelfTransaction='Y'
    BEGIN TRANSACTION --SqlTransaction
END

SELECT 1/0

IF @@ERROR<> 0
BEGIN
    IF @SelfTransaction='Y'
    BEGIN
        ROLLBACK TRANSACTION --SqlTransaction
    END
    RETURN -1 
END
ELSE
BEGIN
    IF @SelfTransaction='Y'
    BEGIN
        COMMIT TRANSACTION --SqlTransaction
    END
    RETURN 0
END

GO

这样，仅当您尚未进行事务处理时，才发出事务处理命令。如果以这种方式对所有过程进行编码，则只有发出BEGINTRANSACTION的过程或C＃代码才会真正发出COMMIT /ROLLBACK，并且事务计数将始终匹配（不会出错）。

在C＃中来自TransactionScope类文档：

static public int CreateTransactionScope(
    string connectString1, string connectString2,
    string commandText1, string commandText2)
{
    // Initialize the return value to zero and create a StringWriter to display results.
    int returnValue = 0;
    System.IO.StringWriter writer = new System.IO.StringWriter();

    try
    {
        // Create the TransactionScope to execute the commands, guaranteeing
        // that both commands can commit or roll back as a single unit of work.
        using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection connection1 = new SqlConnection(connectString1))
            {
                // Opening the connection automatically enlists it in the 
                // TransactionScope as a lightweight transaction.
                connection1.Open();

                // Create the SqlCommand object and execute the first command.
                SqlCommand command1 = new SqlCommand(commandText1, connection1);
                returnValue = command1.ExecuteNonQuery();
                writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

                // If you get here, this means that command1 succeeded. By nesting
                // the using block for connection2 inside that of connection1, you
                // conserve server and network resources as connection2 is opened
                // only when there is a chance that the transaction can commit.   
                using (SqlConnection connection2 = new SqlConnection(connectString2))
                {
                    // The transaction is escalated to a full distributed
                    // transaction when connection2 is opened.
                    connection2.Open();

                    // Execute the second command in the second database.
                    returnValue = 0;
                    SqlCommand command2 = new SqlCommand(commandText2, connection2);
                    returnValue = command2.ExecuteNonQuery();
                    writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                }
            }

            // The Complete method commits the transaction. If an exception has been thrown,
            // Complete is not  called and the transaction is rolled back.
            scope.Complete();
        }
    }
    catch (TransactionAbortedException ex)
    {
        writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
    }
    catch (ApplicationException ex)
    {
        writer.WriteLine("ApplicationException Message: {0}", ex.Message);
    }

    // Display messages.
    Console.WriteLine(writer.ToString());

    return returnValue;
}

只是一个想法，但是您可以使用TransactionAbortedExceptioncatch来获取实际错误，而忽略事务计数不匹配警告。