How to resolve: F_JT0736: Capture encountered DDL statement to create the table [table_name] but table is already enrolled

Tag 30322

Problem Summary

We received the below error. We do not have AdaptDDL enabled for channel. How do we resolve this issue? 

 

F_JT0736: Capture encountered DDL statement to create the table [dbo.customer] but table is already enrolled.

F_JT1492: Previous error occurred during a create_table operation for table [dbo.customer] at SEQ 0x20f9e60307bcc00 and LSN 0020f9e6:000c1ef3:0018 with timestamp [2019-10-25T03:20:11-04:00]. To report this to Technical Support a backup transaction file will be needed,but such a file does not yet exist. Please do the following: (1) Perform another SQL Server transaction log backup of this database. (2) Re-start this capture job again; this error message should change to one which mentions a backup transaction file, but also shows a similar error message on the the same LSN number. (3) Send to Technical Support (a) the backup transaction file, (b) both this error message, and the subsequent error message (which mentions the backup transaction file), and (c) all enroll files matching *.enroll or *.enroll_rev_* in $HVR_CONFIG/router/<hub>/<chn>/loc_<loc> on the hub machine. To skip this transaction's changes to this table, use command {hvrcontrol -c -l <loc> -s -tcontract -whvr_tx_id=0xaf56a0156 -x0 <hub> <chn>}. Afterwards, restart the capture job. The skipped change(s) can be recovered later with on-line refresh; {hvrrefresh -r <loc> -tcontract -qwo <hub> <chn>}.

 

Answer

The below error can occur when the table is recreated on source side but still exists in the target location. When a table is dropped and recreated on the source, the object id is different than what it was originally.  The object id is recorded in the HVR enrollment file which now differs from the target table id therefore giving you this error

To resolve this

Reinitialize your replication by selecting options "Scripts and Jobs" and "Table Enrollment - Replace Old Enrollment" only

 

That will create a new enrollment with the changed object id for the table.