Known Issue: "Create license allocations from ImportedSoftwareLicenseAllocations" import step may fail with error: Violation of UNIQUE KEY constraint 'UQ_SoftwareLicenseAllocation'. Cannot insert duplicate key in object 'dbo.SoftwareLicenseAllocation_MT'. (IOK-931590)

[#1]

Symptoms

The inventory import process may fail with error details like the following appearing in logging:

2022-04-14 09:02:44,748 [INFO ]     Create license allocations from ImportedSoftwareLicenseAllocations
2022-04-14 09:02:49,172 [INFO ]       Failed to execute Writer 'Create license allocations from ImportedSoftwareLicenseAllocations' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\LicenseCreateUpdate.xml, at step line 28
Error: Violation of UNIQUE KEY constraint 'UQ_SoftwareLicenseAllocation'. Cannot insert duplicate key in object 'dbo.SoftwareLicenseAllocation_MT'. The duplicate key value is (1167, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>).
The statement has been terminated.
Added 0 new user allocation(s) from the source database connections.
2022-04-14 09:02:49,172 [INFO ]       All retries have been attempted for Writer 'Create license allocations from ImportedSoftwareLicenseAllocations'
2022-04-14 09:02:49,172 [ERROR]       System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ_SoftwareLicenseAllocation'. Cannot insert duplicate key in object 'dbo.SoftwareLicenseAllocation_MT'. The duplicate key value is (1167, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>, <NULL>).
The statement has been terminated.
Added 0 new user allocation(s) from the source database connections.

Mitigation

A possible cause of this issue is encountering a situation where user or inventory device details are updated by a user or other process while an inventory import process is running. If this occurs it is likely that the same problem will not occur on subsequent inventory import processes, so attempting to run the inventory import process again may result in a success.

Fix status

This issue has been fixed in the following FlexNet Manager Suite releases: 2022 R2 (On Premises), 2022 R2 / Nov 2022 (Cloud)

Other information

Affected components: Inventory import (read/write/export), Software license management

Master issue ID: IOK-931590

Also known as: FNML-76974

[mfranz]

I had this error repeatedly in a customer environment (2020 R1), not only on a daily basis, but also every time I ran the writer manually.

I am not sure what caused this, but I found ImportedUsers with ComplianceUserIDs that were no longer valid. I can only assume that ComplianceUsers were removed and that was not properly translated back to ImportedUser.

One could argue that existing ImportedUsers should still be valid and their ComplianceUser counterpart should not have been deleted, but that's just how it is here.

I solved it by adding a WHERE clause to "Delete license allocations that no longer exist in ImportedSoftwareLicenseAllocations" (step 1170).

WHERE iu.ComplianceUserID IN (SELECT ComplianceUserID FROM ComplianceUser)

The step name is a bit confusing because that temp table #ImportedLicenseUser is later also used for importing teh new stuff.