I get the following error on an insert to the reference table: Msg 6549, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInvoke, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke': System.Data.SqlClient.SqlException: User defined routine, trigger or aggregate tried to rollback a transaction that is not started in that CLR level. An exception will be thrown to prevent execution of rest of the user defined routine, trigger or aggregate. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.ExecuteTransaction(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalTransaction.Rollback() at System.Data.SqlClient.SqlTransaction.Rollback() at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c) . User transaction, if any, will be rolled back.

Hi Steven, Not enough information has been provided regarding the design of your package, so I am shooting in the dark: This issue fits into http://support.microsoft.com/kb/942905 where the resolution offered is in installing Cumulative update package 4 for SQL Server 2005 Service Pack 2 Arthur My Blog

It' s running on SQL 2008 r2, the scenario above is just a simple insert to one of the maintained tables from a query...ie insert into Phone(Id, PhoneNumber) Values (123, '555-1212') where phone is the maintained table. Steven

If the ID field is autoincremented then you will have an error when you try to input that value. So, is the id field set to be auto-incremented.

Hi Steven G Gummings, Have you deleted the maintained match index table ever? We should not delete the maintained match index table before running the sp_FuzzyLookupTableMaintenanceUnInstall stored procedure. If the match index table is deleted, the triggers on the reference table will not execute correctly. All subsequent updates to the reference table will fail until you manually drop the triggers on the reference table. Thanks, Eileen Forum Support Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

No, this is not the case, my auto-inct primary keys do not have values assigned to them.

No, the tables have not been deleted.