TooManyRowsAffectedException with encrypted triggers

Question

I'm using nHibernate to update 2 columns in a table that has 3 encrypted triggers on it. The triggers are not owned by me and I can not make changes to them, so unfortunately I can't SET NOCOUNT ON inside of them.

Is there another way to get around the TooManyRowsAffectedException that is thrown on commit?

Update 1

So far only way I've gotten around the issue is to step around the .Save routine with

var query = session.CreateSQLQuery("update Orders set Notes = :Notes, Status = :Status where OrderId = :Order");
query.SetString("Notes", orderHeader.Notes);
query.SetString("Status", orderHeader.OrderStatus);
query.SetInt32("Order", orderHeader.OrderHeaderId);
query.ExecuteUpdate();

It feels dirty and is not easily to extend, but it doesn't crater.

Answer 1

We had the same problem with a 3rd party Sybase database. Fortunately, after some digging into the NHibernate code and brief discussion with the developers, it seems that there is a straightforward solution that doesn't require changes to NHibernate. The solution is given by Fabio Maulo in this thread in the NHibernate developer group.

To implement this for Sybase we created our own implementation of IBatcherFactory, inherited from NonBatchingBatcher and overrode the AddToBatch() method to remove the call to VerifyOutcomeNonBatched() on the provided IExpectation object:

public class NonVerifyingBatcherFactory : IBatcherFactory
{
    public virtual IBatcher CreateBatcher(ConnectionManager connectionManager, IInterceptor interceptor)
    {
        return new NonBatchingBatcherWithoutVerification(connectionManager, interceptor);
    }
}

public class NonBatchingBatcherWithoutVerification : NonBatchingBatcher
{
    public NonBatchingBatcherWithoutVerification(ConnectionManager connectionManager, IInterceptor interceptor) : base(connectionManager, interceptor)
    {}

    public override void AddToBatch(IExpectation expectation)
    {
        IDbCommand cmd = CurrentCommand;
        ExecuteNonQuery(cmd);
        // Removed the following line
        //expectation.VerifyOutcomeNonBatched(rowCount, cmd);
    }
}

To do the same for SQL Server you would need to inherit from SqlClientBatchingBatcher, override DoExectuteBatch() and remove the call to VerifyOutcomeBatched() from the Expectations object:

public class NonBatchingBatcherWithoutVerification : SqlClientBatchingBatcher
{
    public NonBatchingBatcherWithoutVerification(ConnectionManager connectionManager, IInterceptor interceptor) : base(connectionManager, interceptor)
    {}

    protected override void DoExecuteBatch(IDbCommand ps)
    {
        log.DebugFormat("Executing batch");
        CheckReaders();
        Prepare(currentBatch.BatchCommand);
        if (Factory.Settings.SqlStatementLogger.IsDebugEnabled)
        {
            Factory.Settings.SqlStatementLogger.LogBatchCommand(currentBatchCommandsLog.ToString());
            currentBatchCommandsLog = new StringBuilder().AppendLine("Batch commands:");
        }

        int rowsAffected = currentBatch.ExecuteNonQuery();

        // Removed the following line
        //Expectations.VerifyOutcomeBatched(totalExpectedRowsAffected, rowsAffected);

        currentBatch.Dispose();
        totalExpectedRowsAffected = 0;
        currentBatch = new SqlClientSqlCommandSet();
    }
}

Now you need to inject your new classes into NHibernate. There are at two ways to do this that I am aware of:

1. Provide the name of your IBatcherFactory implementation in the adonet.factory_class configuration property
2. Create a custom driver that implements the IEmbeddedBatcherFactoryProvider interface

Given that we already had a custom driver in our project to work around Sybase 12 ANSI string problems it was a straightforward change to implement the interface as follows:

public class DriverWithCustomBatcherFactory : SybaseAdoNet12ClientDriver, IEmbeddedBatcherFactoryProvider
{
    public Type BatcherFactoryClass
    {
        get { return typeof(NonVerifyingBatcherFactory); }
    }

    //...other driver code for our project...
}

The driver can be configured by providing the driver name using the connection.driver_class configuration property. We wanted to use Fluent NHibernate and it can be done using Fluent as follows:

public class SybaseConfiguration : PersistenceConfiguration<SybaseConfiguration, SybaseConnectionStringBuilder>
{
    SybaseConfiguration()
    {
        Driver<DriverWithCustomBatcherFactory>();
        AdoNetBatchSize(1); // This is required to use our new batcher
    }

    /// <summary>
    /// The dialect to use
    /// </summary>
    public static SybaseConfiguration SybaseDialect
    {
        get
        {
            return new SybaseConfiguration()
                .Dialect<SybaseAdoNet12Dialect>();
        }
    }
}

and when creating the session factory we use this new class as follows:

var sf = Fluently.Configure()
    .Database(SybaseConfiguration.SybaseDialect.ConnectionString(_connectionString))
    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<MyEntity>())
    .BuildSessionFactory();

Finally you need to set the adonet.batch_size property to 1 to ensure that your new batcher class is used. In Fluent NHibernate this is done using the AdoNetBatchSize() method in a class that inherits from PersistenceConfiguration (see the SybaseConfiguration class constructor above for an example of this).

Answer 2

er... you might be able to decrypt them...

Edit: if you can't change code, decrypt, or disable then you have no code options on the SQL Server side.

However, You could try "disallow results from triggers Option" which is OK for SQL 2005 and SQL 2008 but will be removed in later versions. I don't know if it suppresses rowcount messages though.