Missing INSERT statement from MSSQL -> Snowflake using Logstream.

[rapmaster]

I've had this issue twice now. Somehow the INSERT statement for a certain table was lost hence the attrep_apply_exceptions table reports the error that the UPDATE statement failed to update the required row. 

We are using two tasks,  one from MSSQL to Log Stream running continuously and Log Stream to Snowflake running on schedule. 

Is there a way to search the LogStream database to find if the INSERT statement ever existed for the given record? 

We did reboot the Qlik instance within the time window however I understand this should continue from the last log position in SQL so in theory we shouldn't drop any records. 

[Heinvandenheuvel]

I suppose you could run 'store changes' task in parallel to try to get to the bottom of this the issue.

Or you could switch the error handling to 'insert if row to be updated is not found' and be happy because that's what you'll end up doing anyway.

Hein.

[rapmaster]

Thanks, but "insert if row" will only work if all values are present in the UPDATE statement which I'm not sure is true at the moment. 

[Heinvandenheuvel]

All columns will be present as Replicate always updates all columns in bulk mode. It has to because it uses a single update  statement per table regardless of which transaction the updates came from therefor irrespective of which columns where actually update. To make this possible it requests supplemental logging for the source if needed such as for Oracle

[rapmaster]

We've experienced this issue again. UPDATE statement failed hence missing the  INSERT statement. 

Weirdly, it's only one record from the table in question, which suggests it's not the transaction log missing but some failure between MSSQL -> LogStream -> Snowflake. 

The record in question was replicated to another server via SQL Transaction replication to rule out the transaction log issue.

What logging exists for LogStream? 

[deepaksahirwar]

Dear @rapmaster ,

I have few solutions for your queries. 

1. You could try to run a ‘store changes’ task in parallel to track the changes in the source and target databases. This might help you identify if the INSERT statement was ever executed or not.

2. You could also check the permissions of the user that Replicate runs as, and make sure they have access to the LogStream storage path. If not, you might need to grant the CREATE ANY DIRECTORY privilege for the Oracle source.

Alternatively, you could switch the error handling to 'insert if row to be updated is not found’. This might resolve the error and insert the missing row in the target table.

I hope this is helpful for you. 😊

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Best Regards,

Deepak