Creating Table Trigger to maintain FuzzyLookup Index

Hi,

I've created initial indexes for my table for the fuzzylookup process. I clicked on "Maintained index" but I don't see any triggers created on the reference table.

Do I create the triggers to maintain indexes myself?

Does anybody know how to create these triggers in terms of schema_name, Data_Modification_Statements etc.?

Would it be "Alter index <index name> REBUILD command?

Appreciate the help.

Gulden

Perhaps you don't have permissions to create the triggers? The "indexes" are index tables, not regular indexes. I'm pretty sure you want it to create the triggers itself. I see there is a sp_FuzzyLookupTableMaintenanceInstall procedure in the master database that you might try. The sp_FuzzyLookupTableMaintenanceUnInstall procedure is documented for removing the triggers, so I'd bet the other one creates them.
|||

Thank you for your reply.

I realized that I was pointing to the development database when I created fuzzy lookup indexes.

I re-created them in the staging database and I can see triggers and index files.

But I cannot run any type of maintenance on them.

I tried running sp_FuzzyLookupTableMaintenanceUnInstall sp_FuzzyLookupTableMaintenanceInvoke

But I get the following error on a recently created index.

A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

System.Data.SqlClient.SqlException:

Now I am concern that in case of an index corruption in the future I won't be able to uninstall them.

Do you think Just deleting indexes and triggers would work?

Thanks,

|||I don't know what sp_FuzzyLookupTableMaintenanceInvoke does. Do you? Does the UnInstall procedure give that error too? If so, it wouldn't seem that you have any choice but to delete the triggers and index manually. The docs warn that if you delete the table before the triggers, any statements against the reference table will fail until the triggers are removed.
|||

Turned out that I don't have permissions to run those stored procs.

My database admin ran them and got successfully uninstall the triggers.

I had to uninstall them because in the managed code it generated

InsertNonMatchedToClientMap Exception:A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.

I think I need to change my database connection strings but we will install in production soon.

So I am back to "Create Index" option on the fuzzy lookup transforms.

Thank you very much for you replies and support....

|||

I receive the following error when selecting the "Maintain Index" option within my SSIS package.

Error: 0xC0202009 at Data Flow Task, Fuzzy Lookup [645]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException: Unable to parse token counts in Error Tolerant Index metadata. The index is probably corrupt.

I am running as "sa" but still don't seem to have permission to execute the SPs. I receive the the following error when executing sp_FuzzyLookupTableMaintenanceInstall from within SQL Management Studio:

Msg 6522, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInstall, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":

System.Data.SqlClient.SqlException: Could not retrieve metadata from Error Tolerant Index. The index is probably corrupt.

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 Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.RaiseErrorId(SqlCommand cmd, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.ReportErrors(SqlCommand cmd, ExceptionType Type, String ErrorMessage, FltmErrorMsgId MsgId, FltmErrorState State, SqlServerSeverity Severity, SqlErrorCollection errors)

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)

.

Any ideas?