Data entity error "Change Tracking Query error in D365 Finance and Operations Data Export" D365FO

Change Tracking Query error in D365 Finance and Operations Data Export

Recently there was an issue with Change Tracking for BYOD. We keep getting the following error even after Change Tracking is enabled for the data entity.

"Incremental push is not supported for entity X, as change tracking query is not enabled on it."

Solution :

After debugging I found that the system will check if the record exists in the table "AifSqlCdcEnabledTables" with the field TableName and return int (1/0).

Run the below query in SQL to find if the list contains your table.

 select RecId, * from AifSqlCdcEnabledTables       where AifSqlCdcEnabledTables.TableName = 'XXXXX'

In my case, the Primary table was missing and causing the issue, whereas the secondary tables are present in the list. So I have changed the Entity to make the Primary Data source as secondary and secondary as Primary and the issue is fixed.

SQL Command to Enable and Disable Change Tracking (SQL Server) + D365 FO

 Below are the commands used to enable/disable change tracking in the SQL. 

1. Enable change tracking on the database

ALTER DATABASE AXDBNAME  

SET CHANGE_TRACKING = ON  

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

2. Enable Change Tracking for a Table

ALTER TABLE TableName

ENABLE CHANGE_TRACKING  

WITH (TRACK_COLUMNS_UPDATED = ON)

3. Disable Change Tracking for a Database 

ALTER DATABASE AdventureWorks2012  

SET CHANGE_TRACKING = OFF

4. Disable Change Tracking for a Table

ALTER TABLE TableName  

DISABLE CHANGE_TRACKING;