Reconciliation failing with error The MERGE statement conflicted with the FOREIGN KEY constraint "FK_FileEvidenceMatchCount_FileEvidence_MT". The conflict occurred in database "FNMSCompliance", table "dbo.NewFileEvidence_MT", column 'FileEvidenceID'. Warning: Null value is eliminated by an aggregate or other SET operation. The statement has been terminated.

[winvarma_MCI]

Hi Forum,

We are having issue with reconciliation with the error. 

Failed to import inventory devices with error message

The MERGE statement conflicted with the FOREIGN KEY constraint "FK_FileEvidenceMatchCount_FileEvidence_MT". The conflict occurred in database "FNMSCompliance", table "dbo.NewFileEvidence_MT", column 'FileEvidenceID'. Warning: Null value is eliminated by an aggregate or other SET operation. The statement has been terminated.

Please suggest how this issue can be fixed.

Regards,

winvarma

[ChrisG]

I can't find any record of an error quite like this being observed before.

Does the error occur every time the import runs?

What import step is being executed when this error occurs? (This step will be identified in the import log prior to the error message you have quoted.)

[winvarma_MCI]

Hi @ChrisG ,

thanks for the quick revert on this thread. Actually till yesterday we are facing reconciliation issues with the below error. 

'Failed to import inventory devices with error message 'Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.'

As a part of troubleshooting steps we have executed the NewFileEvidence_MT reseed.sql query as per the below instructions from the support last August on the same issue.

"It appears that you are using an outdated reseed script. I've seen a reported issue where the error you've encountered can be seen with the outdated reseed script. Please run the new reseed script in order to resolve this:

1) Back up the Compliance DB.

2) Disable the batch scheduler with the following query:
UPDATE
ComplianceSetting
SET
SettingValue = 0
WHERE
SettingName = 'BatchSchedulerProcessDispatch'
;

3) Run the attached query NewFileEvidence_MT reseed v5.sql

4) Re-enable reconciles:
UPDATE
ComplianceSetting
SET
SettingValue = 1
WHERE
SettingName = 'BatchSchedulerProcessDispatch'
;

Later after following the above instructions we are getting the new error which is posted in the initial thread. Attached the SQL query we executed for solving the above issue.

Write file evidence is being executed before this error attached the snapshot.

Have raised a case on this and as there is no update and as this is a production environment in which we are facing the issue we have followed the above steps.

Please review and suggest

[ChrisG]

I understand that you have worked further Flexera Support to get this addressed with an updated import procedures configuration file.

For reference, this is now being tracked as the following Known Issue: WriteRemoteFileEvidenceMatchCounts import writer step may fail with error: Failed to import inventory devices with error message 'The MERGE statement conflicted with the FOREIGN KEY constraint "FK_FileEvidenceMatchCount_FileEvidence_MT". (IOK-1012974)