Qlik Replicate: The metadata for source table 'table name' is different than the corresponding MS-CDC Change Table.

[Steve_Nguyen]

The following warning is logged in the Qlik Replicate log files:

The metadata for source table 'table name' is different than the corresponding MS-CDC Change Table. The table will be suspended. 

Resolution

Multiple root causes and solutions exist.

Temporal or System tables

Temporal and System tables are not supported. See, for example, Limitations and considerations.

If you want to capture changes to these tables with MS-CDC and Qlik Replicate, then you have to unhide the system-generated columns:

ALTER TABLE <the table name> ALTER COLUMN [SysStartTime] drop HIDDEN;

ALTER TABLE <the table name> ALTER COLUMN [SysEndTime] drop HIDDEN;

Depending on the way the table was created, the hidden column names may be different, such as ValidFrom, ValidTo.

If you don't want to make the above change, you can use the ODBC with CDC endpoint and capture both the base table and the history table using SysStartTime as the context column.

The base table and the CDC table on MS-CDC have changed

When the source table is Truncated 

1. Stop the task
2. Disable CDC for the table on the Source  database, which may require your DBA to run the query
   
   Example:
   
   exec sys.sp_cdc_disable_table  
       @Source_schema = 'dbo_schema', @Source_name = 'EMPLOYEE2_table', @capture_instance = 'all'
   
   
3. Then, from the source database, truncate the table (the example is for the table EMPLOYEE2_table) 
4. Then, from the target database, truncate the same table as the source. (or do a reload of the table after resuming the task, assuming that your task is set to truncate on full load.)
5. Resume the task

When the source table uses the DDL Change Handling feature

1. On the source database, do the ALTER DDL change to the table  (the example is for table EMPLOYEE2_table) 
2. Stop the task
3. Disable CDC for the table on the Source  database, which may require your DBA to run the query
   
   Example:
   
   exec sys.sp_cdc_disable_table  
       @Source_schema = 'dbo_schema', @Source_name = 'EMPLOYEE2_table', @capture_instance = 'all'
   
   
4. Resume the task
5. If the table was suspended, then unsuspend the table or reload the table. 
   
   

   Only the first DDL change will be picked up, will need to follow 'Truncation' steps if a DDL change is done while the table is suspended.

When the source table uses the alternate source table DDL Change Handling feature

1. Stop the task
2. Disable CDC for the table on the Source  database, may require your DBA to run the query
   
   Example:
   
   exec sys.sp_cdc_disable_table  
       @Source_schema = 'dbo_schema', @Source_name = 'EMPLOYEE2_table', @capture_instance = 'all'
   
   
3. On the source database, modify the source table (the example is for table EMPLOYEE2_table) 
4. On the target database, modify the target table (the example is for table EMPLOYEE2_table) 
5. From the task, click on the drop-down next to run, select the advanced run option, and select: Start the task with metadata only run (Create missing tables and then stop). This will refresh the internal metadata without losing position.
6. Enable MS-CDC for the table (if the task is not set to do it automatically)
7. Resume the task
   
   If multiple tables need to be disabled, disable the CDC database.
   
   Example (disable):
   
   exec demo_s_database.sys.sp_cdc_disable_db;
   
   Example (enable):
   
   exec demo_s.dbo.sp_cdc_enable_db;

For SQL Server 2014

Option 1: Upgrade SQL to the latest version

Option 2: Add a column below to each CDC table:

1. Run this example:
   
   Alter table table_cdc
   Add [__$command_id] int
   
   
2. Unsuspend all tables
3. Uncheck the capture DDL events in the advanced table of the source MS-CDC, then save
4. Reload the task
5. The task will run as normal with CDC

Environment

Qlik Replicate 
MS-CDC source endpoint