Known Issue: CreateVDIUserConnections writer step may fail with error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VDIUserConnection_ComplianceUser". (IOJ-2258826)
Known Issue: CreateVDIUserConnections writer step may fail with error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VDIUserConnection_ComplianceUser". (IOJ-2258826)
Summary
The CreateVDIUserConnections import writer step may fail with the following error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VDIUserConnection_ComplianceUser". The conflict occurred in database "FNMSCompliance", table "dbo.ComplianceUser_MT", column 'ComplianceUserID'.
Details
This issue is caused by a logic error that incorrectly handles some situations where user records have been deleted from the database.
When this issue occurs, logging from the import writer process will show error details similar to the following:
2022-04-21 16:09:03,488 [INFO ] CreateVDIUserConnections
2022-04-21 16:17:15,655 [INFO ] Failed to execute Writer 'CreateVDIUserConnections' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\VDI.xml, at step line 78
Error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VDIUserConnection_ComplianceUser". The conflict occurred in database "FNMSCompliance", table "dbo.ComplianceUser_MT", column 'ComplianceUserID'.
Removed 0 VDI user connection records for users who no longer have access to VDIs
Updated inventory date for 0 VDI user connection records.
The statement has been terminated.
Added 0 new VDI user connection records for users who have access to VDIs
2022-04-21 16:17:15,655 [INFO ] All retries have been attempted for Writer 'CreateVDIUserConnections'
2022-04-21 16:17:15,717 [ERROR] System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VDIUserConnection_ComplianceUser". The conflict occurred in database "FNMSCompliance", table "dbo.ComplianceUser_MT", column 'ComplianceUserID'.
Workaround
This issue can be worked around by making adding a line in one of the SQL queries in the CreateVDIUserConnections writer in the C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\VDI.xml file on the FlexNet Manager Suite batch server as illustrated in the following unified diff:
--- VDI.xml.orig
+++ VDI.xml @@ -560,11 +560,12 @@
INSERT INTO dbo.VDIUserConnection (VDIGroupID, ComplianceUserID, ComplianceConnectionID, InventoryDate)
SELECT
tvuc.VDIGroupID,
tvuc.ComplianceUserID,
tvuc.ComplianceConnectionID,
tvuc.InventoryDate
FROM #VDIUserConnection AS tvuc
+ JOIN dbo.ComplianceUser cu ON cu.ComplianceUserID = tvuc.ComplianceUserID
WHERE NOT EXISTS (
SELECT 'x'
FROM dbo.VDIUserConnection AS vuc
WHERE
Fix status
This issue has been fixed in the following FlexNet Manager Suite release: 2022 R1.1 / May 2022 (Cloud)
Tip: If you have been impacted by this issue, please click the KUDOS button above. This helps to track the relative significance and importance of issues. Clicking on an option against "Was this article helpful?" also helps assess and improve the usefulness of content.
