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 ().
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.
Share your comments below if this helps you.
Should I first run this you mean:
ReplyDeleteSELECT Distinct T1.NAME AS BUDGETMODEL into ##Name FROM SYSOBJECTS T1, sys.change_tracking_tables T2 WHERE T1.ID = T2.object_id ORDER BY T1.NAME
Then this:
SELECT 'ALTER TABLE ' + BUDGETMODEL + ' ENABLE CHANGE_TRACKING' from ##Name
Then this:
SELECT 'ALTER TABLE ' + BUDGETMODEL + ' DISABLE CHANGE_TRACKING' from ##Name
SELECT Distinct T1.NAME AS BUDGETMODEL into ##Name FROM SYSOBJECTS T1, sys.change_tracking_tables T2 WHERE T1.ID = T2.object_id ORDER BY T1.NAME
ReplyDeleteThen this:
SELECT 'ALTER TABLE ' + BUDGETMODEL + ' ENABLE CHANGE_TRACKING' from ##Name
1) Run above
2) Copy the text
3) Then this:
SELECT 'ALTER TABLE ' + BUDGETMODEL + ' DISABLE CHANGE_TRACKING' from ##Name
4) Copy the text as backup
5) Now, Run the text copied on point 2