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

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, 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 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, assume that your task is set to truncate on full load.)
5. Resume the task

When source table uses 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, 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 source table uses 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 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 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 below column 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 advance 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

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.