Cannot delete a Search (4362761)

Before performing these steps make a backup of your database.

Before doing this backup the Change Auditor database.

1. Stop the Change Auditor Coordinator(s) and all instances of the Clients
2. Remove large number of erroneous records from [Query].[Def] that have no Search definition defined.
   • Open SQL Management Studio
   • Connect to the SQL Server that contains the ChangeAuditor database
   • Click 'New Query'
   • Select the ChangeAuditor database from the database dropdown box
   • Paste the following command in the Query window:
     • Delete FROM [Query].[Def] where QuerySQL IS NULL
3. Remove records from Query.Information that do not have matching definiations in [Query].[Def]. These are Searches that were partially deleted in the Client.
   • Click 'New Query'
   • Select the ChangeAuditor database from the database dropdown box
   • Paste the following command in the Query window:
     • DELETE FROM Query.Information
     • WHERE queryid NOT IN (SELECT queryid FROM query.def)
   • Click Execute
4. Alter [Query].[Information] to add a forign key constaint with Query.Def
   • Click 'New Query'
   • Select the ChangeAuditor database from the database dropdown box
   • Paste the following command in the Query window:
     • ALTER TABLE [Query].[Information]  WITH CHECK ADD  CONSTRAINT [FK_QueryInformation_QueryDef] FOREIGN KEY([QueryID]) REFERENCES [Query].[Def] ([QueryID]) ON DELETE CASCADE
     • ALTER TABLE [Query].[Information] CHECK CONSTRAINT [FK_QueryInformation_QueryDef]
   • Click Execute
5. Start the Coordinator and the Client
6. Confirm Searches can be Deleted