FME fails to catch failure of MS/SQL Server INSERT statements
Hi.
I’ve encountered this before, but now it blocks the saving of a 28K large table, and is so no more a mere nuisance.
It seems that FME fails to catch that SQL statements fails typically due to constraints in the database. FME happily reports that all posts were saved, although none were in this case.
My latest error message from the log file looks like this:
Microsoft SQL Server Spatial Writer: Failed to write a feature of type `VEJLE_GADELYS.Elledning' to the database. Provider error `(-2147217873) The INSERT statement conflicted with the FOREIGN KEY constraint ...
Because FME fails to catch these errors, and thus not failing in the automation, we’re putting invalid datasets into production.
This is bad.
Page 1 / 1
Hi @lifalin2016, it’s difficult to say what is happening without some more information.
Are you using bulk mode? Is FME rolling back the transaction or not?
Are you willing to share the job log here so I can see more of what’s going on? Alternatively, if you would rather share more information privately you can submit a support ticket and upload the information there.
Hi @lifalin2016,
In addition to TandraAtSafe’s response...
Is this using the Writer or FeatureWriter?
Could you also check how the workspace is instructed to handle failures?
Please confirm the version of FME involved as well.
I’m thinking because their is no rejection it is continuing. Thanks
Thanks Tandra and Steve.
Ok, let me clarify.
The error occurs inside the (MS/SQL) writer, not in the workspace. And so setting “Rejected Features Handling” will not be of relevance, unless I misunderstand the option setting.
And the errors _are_ logged, but only as a warning, not as an error.
The problem is that the workspace doesn’t fail, and can’t be made to do so afaik, if these errors occur, and so they may be overlooked. It was only because every single INSERT failed, and the resulting dataset ended up being empty, that we took notice.
Maybe an option similar to “Rejected Features Handling”, or in the writer itself ?
Ps!
The error that prompted all the failed INSERTs have been found and fixed. I tried to insert a zero (0) into a “uniqueidentifier” field, which is not allowed. Unfortunately, the error message was inprecise and somewhat misleading, so it took a while to track down.
But such errors, that may very well be more subtle in nature, will probably also happen in the future. This is what worries me, as we don’t have the time to micro-manage each single daily import job this way.
I may have to think in terms of a log scanner for this specific error.
I’ve discovered that in addition to the log file, FME produces a small FFS file (in the log folder) with the rejected data features.
I’ll see if I can incorporate these in my translation result reporting.
FME 2022.6
...
I’ll see if I can incorporate these in my translation result reporting.
I did manage to incorporate the existence of an FFS file next to the log file in my logic to issue a “data missing” email warning.
So let’s park the issue here, and hope the “warnings” may be escalated to a full error in a future version.
