Skip to main content
Solved

FME fails to catch failure of MS/SQL Server INSERT statements

  • May 22, 2024
  • 5 replies
  • 221 views
lifalin2016
Supporter
Forum|alt.badge.img+38

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.

Best answer by lifalin2016

...

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.

 

-- Cheers, Lars I.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

TandraAtSafe
Safer
Forum|alt.badge.img+7
  • TandraAtSafeSafer
  • Safer
  • 24 replies
  • May 23, 2024

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. 

Tandra G (Technical Support Specialist)
steveatsafe
Safer
Forum|alt.badge.img+13
  • steveatsafeSafer
  • Safer
  • 422 replies
  • May 23, 2024

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

SteveatSafe
lifalin2016
Supporter
Forum|alt.badge.img+38
  • lifalin2016Supporter
  • Author
  • Supporter
  • 592 replies
  • May 24, 2024

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.

-- Cheers, Lars I.
lifalin2016
Supporter
Forum|alt.badge.img+38
  • lifalin2016Supporter
  • Author
  • Supporter
  • 592 replies
  • May 28, 2024

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

-- Cheers, Lars I.
lifalin2016
Supporter
Forum|alt.badge.img+38
  • lifalin2016Supporter
  • Author
  • Supporter
  • 592 replies
  • Best Answer
  • June 13, 2024

...

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.

 

-- Cheers, Lars I.

Community Stats

32,100
Posts
121,896
Replies
39,746
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.