Known Issue: CreateVDIUserConnections writer step may fail with error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VDIUserConnection_ComplianceUser". (IOJ-2258826)

[#1]

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)

Other information

Affected components: Integration: Citrix, Inventory import (read/write/export)

Master issue ID: IOJ-2258826

Also known as: FNML-75767