The primary key constraint 'I_029TYPEIDX' on table 'BUDGETMODEL' cannot be dropped because change tracking is enabled on the table

AX 2012 upgrade

During upgrade AX 2012 to AX 2012 R3. i face the error below while synchronize data dictionary on development server

Error

SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The primary key constraint 'I_029TYPEIDX' on table 'BUDGETMODEL' cannot be dropped because change tracking is enabled on the table. Change tracking requires a primary key constraint on the table. Disable change tracking before dropping the constraint.

SQL statement: ALTER TABLE BUDGETMODEL DROP CONSTRAINT I_029TYPEIDX

Cannot execute a data definition language command on  ().

Solution

- Open SQL Management studio and open new query window for the AX database

- Run below query. this will populate the table having change Tracking enabled

- set the query window to result in to text

SELECT Distinct T1.NAME AS TABLENAME into ##Name FROM SYSOBJECTS T1, sys.change_tracking_tables T2 WHERE T1.ID = T2.object_id ORDER BY T1.NAME 

SELECT 'ALTER TABLE ' + TableName + ' DISABLE CHANGE_TRACKING' from ##Name

- Copy the text and save to file. do not run this now

- execute query below

SELECT 'ALTER TABLE ' + TableName + ' ENABLE CHANGE_TRACKING' from ##Name

- Copy the text and save to file

- Run the text saved first

- Restart AOS and Synchronize

that's it.

Reference https://technet.microsoft.com/en-in/library/jj733502.aspx go to AOS fails to start

Share your comments below if this helps you.