Coordinator database upgrade fails with error, "The label token has already been declared" (4234530)

Coordinator will not upgrade the Change Auditor Database with the following error around 30% mark as found in the logs:

Error incorrect syntax near

The label 'token' has already been declared.

Label names must be unique within a query batch or stored procedure.

• Another application which requires a SQL Database was pointed at the Change Auditor Database and executed scripts which modified the Change Auditor Schema
• Once the Schema is modified, the installer will identify it as a fatal error and the upgrade will be cancelled
• This will have the effect of preventing any Client or Coordinator from connecting the the DB anymore

How to confirm the issue:

1. Open SQL Server Management Studio
2. Select the Change Auditor Database
3. Run the following Script
   • SELECT * FROM ChangeAuditor.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
4. Replace the ChangeAuditor by the name of the Database
5. Compare to a normal working DB for extra tables (this step might require assistance from Technical Support)

Workaround

• If the issue is caught directly after the Schema is modified

1. Stop the Coordinator service
2. Restore known good copy of the Change Auditor database from backup
3. Proceed with the upgrade once again

• If the issue occurred before a backup was available or too long ago to accept the loss of data

1. Stop the Coordinator services across the environment
2. Restore a known good copy of the Change Auditor database from backup
3. Restart one Coordinator only still in the pre-upgrade version
4. Extract all Searches and Configuration from the old database
5. Create a new database by changing the name of the database in the Coordinator Configuration
6. Upgrade to the desired version of Change Auditor
7. Import Searches and Configuration in the new DB
8. Keep only a Client in the pre-upgraded version to access the old Database as an Archive DB
9. Upgrade the rest of the Coordinators after making sure to have replicated the new Database name

Notes:

• If the Database Schema is corrupted with extra tables, the upgrade process won't be able to proceed.
• However this will also lock the Database and prevent any Coordinator or Client, whatever the reason from connecting to the DB.
• As such, make sure not to allow any Client or Coordinator from more recent version to connect to that DB if it is still required for reporting