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.
Thank you for a very detailed post. However, I do not see an actual idea presented in the post. Please refer to the submission guidelines for information on how to structure your request.
As you can read from the post, it is a bug that has to be resolved. I got advise from Qlik to post thist problem as an idea because it wont be resolved as a bug, why ever. I don't know the source of Qlik Replicate. So its not easy to give an idea for the solution. Minimum solution I expect is, that if the trigger throws an exception, there is alway a warning in Qlik Replicate. In the same as it allready works at the first transaction.
