Actually, we have the situation that in a Task, we activated Store Changes in Audit table. Source ist Oracle, destination is SQL Server. On the audit table on SQL Server we activated a trigger.
If there is an error in the trigger, it is almost impossible to determine, there is an error at replication of the data. Error handling of Qlik Replicate is not correct (confirmed by Qlik in Support case 01915075). The first time, the errors is shown in Qlik Replicate Log, the second time it happens, there is no alert in Qlik Replicate.
In all the cases, the data in the destination table is replicated but is missing in audit table.
Following, the test-constellations:
Qlik Replicate 6.6.0
Test 1:
Prerequisites:
- Trigger on Change-Log-Table
- Error in Trigger
- No insert before Error
- No try/catch -> Error directly from SQL Server, no user exception
Result:
- After the first transaction: Error-message in Attunity Log, task is running after error, insert of the generated records on destination, no entry in change-log-table -> OK
- After the second transaction: No more error-message in Attunity Log, task is running after error, insert of the generated records on destination, no entry in change-log-table -> NOK because of missing error-message. After a restart of the task, the error-message is written again but only for the first transaction.
Test 2:
Prerequisites:
- Trigger on Change-Log-Table
- Error in trigger
- Insert before Error out of trigger in a different table
- No try/catch -> Error directly from SQL Server, no user exception
Result:
- No error-message in Attunity Log, task is running after error, insert of the generated records on destination, no entry in change-log-table -> NOK because of missing error-message.
Test 3:
Prerequisites:
- Trigger on Change-Log-Table
- Error in Trigger
- No insert before Error
- With try/catch -> user exception (Error-Message > 50000)
Result:
- No error-message in Attunity Log, task is running after error, insert of the generated records on destination, no entry in change-log-table -> NOK because of missing error-message.
Test 4:
Prerequisites:
- Trigger on Change-Log-Table
- Error in procedure that is executed out of the trigger -> same session as Attunity Replicate/Trigger
- Insert before Error
- No try/catch in procedure -> Error directly from SQL Server, no user exception
Result:
- After the first transaction: Error-message in Attunity Log, task is running after error, insert of the generated records on destination and no entry in change-log-table -> OK
- After the second transaction: No more error-message in Attunity Log, task is running after error, insert of the generated records on destination and no entry in change-log-table -> NOK because of missing error-message. After a restart of the task, the error-message is written for the first transaction.
Test 5:
Prerequisites:
- Trigger on Change-Log-Table
- Error in procedure that is executed out of the trigger -> same session as Attunity/Trigger
- Insert before Error
- With try/catch -> user exception (Error-Message > 50000)
Result:
No error-message in Attunity Log, task is running after error, insert of the generated records on destination, no entry in change-log-table -> NOK because of missing error-message.