TransactionScope и транзакции

В SQL Server 2005 было проведено масштабное обновление обработки ошибок. Эти статьи довольно обширны: Error Handling in SQL 2005 and Later by Erland Sommarskog и Error Handling in SQL 2000 - a Background by Erland 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 и выше. Без использования блоков TRY-CATCH в SQL Server 2005 вам будет очень трудно удалить все сообщения, которые SQL Server посылает обратно. дополнительные сообщения, о которых вы говорите, вызваны характером обработки откатов с помощью @@trancount:

от http://www.sommarskog.se/error-handling-I.html#trancount

@@trancount является глобальной переменной, которая отражает уровень вложенных транзакций. Каждая транзакция BEGIN TRANSACTION увеличивает @@trancount на 1, а каждая COMMIT TRANSACTION уменьшает @@trancount на 1. Ничего не фиксируется до тех пор. пока @@trancount не достигнет 0. ТРАНЗАКЦИЯ ROLLBACK откатывает все к крайнему BEGIN TRANSACTION (если только вы не использовали довольно экзотическую функцию SAVE 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

Таким образом, команды транзакции выдаются только в том случае, если вы еще не находитесь в транзакции. Если вы закодируете все свои процедуры таким образом, только та процедура или код C#, которая выдает команду BEGIN TRANSACTION, будет действительно выдавать COMMIT/ROLLBACK, и счетчики транзакций всегда будут совпадать (вы не получите ошибку).

в C# из TransactionScope Class Documentation:

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;
}

Просто мысль, но вы могли бы использовать TransactionAbortedException catch для получения фактической ошибки и игнорирования предупреждения о несоответствии количества транзакций.