FNMS 2023 R1 - Reconciliation Error - Failed to import inventory devices with error message 'Violation of UNIQUE KEY constraint 'UQ_VMHostProperty'. Cannot insert duplicate key in object 'dbo.VMHostProperty_MT'. The duplicate key value is (2684642, 2). The statement has been terminated.

[mschwach]

Hi, has anyone an idea ?

2023-10-06 11:22:58,925 [INFO ] Populate VMHostProperty
2023-10-06 11:22:59,472 [INFO ] Failed to execute Writer 'Populate VMHostProperty' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\VirtualMachine.xml, at step line 77
Error: Violation of UNIQUE KEY constraint 'UQ_VMHostProperty'. Cannot insert duplicate key in object 'dbo.VMHostProperty_MT'. The duplicate key value is (2684642, 2).
The statement has been terminated.
Added 0 VM Host Property
Deleted 0 VM Host Property
2023-10-06 11:22:59,472 [INFO ] All retries have been attempted for Writer 'Populate VMHostProperty'
2023-10-06 11:22:59,504 [ERROR] System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_VMHostProperty'. Cannot insert duplicate key in object 'dbo.VMHostProperty_MT'. The duplicate key value is (2684642, 2).
The statement has been terminated.
Added 0 VM Host Property
Deleted 0 VM Host Property
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ManageSoft.Compliance.Importer.Logic.XML.Writer.Execute(IExecutionContext context)
at ManageSoft.Compliance.Importer.Logic.ComplianceImporter.ExecuteWriters(ComplianceReader p_ComplianceReader, IExecutionContext context, String singleConnectionIdentifier)
ClientConnectionId:c6c0a9ed-15d3-4685-b2ee-503c9fcee09e
Error Number:2627,State:1,Class:14

[mschwach · Accepted solution]

Hi,

don't know why, but the ID 2684642 couldn't be found in these tables.
So we did a backup of dbo.VMHostProperty_MT and then:

truncate table dbo.VMHostProperty_MT
and
truncate table dbo.ImportedVMHostProperty_MT

This has fixed the issue for us and Reconciliation is working again.

regards 🙂