Failed to execute Writer 'RemoteApplicationsByFileEvidenceTempTables' due to inserting duplicate key in object 'dbo.#RemoteServerFileEvidence'

[#1]

Summary

Failed to execute Writer 'RemoteApplicationsByFileEvidenceTempTables' due to inserting duplicate key in object 'dbo.#RemoteServerFileEvidence'

Symptoms

Reconcile fails after XenApp Agent integration on Writer 'RemoteApplicationsByFileEvidenceTempTables' due to inserting duplicate key in object 'dbo.#RemoteServerFileEvidence'.

2016-07-20 00:16:18,953 [INFO ] Successfully processed in 3 seconds
2016-07-20 00:16:18,953 [INFO ] RemoteApplicationsByFileEvidenceTempTables
2016-07-20 00:16:19,500 [INFO ] Failed to execute Writer 'RemoteApplicationsByFileEvidenceTempTables' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\InstalledSoftware.xml
Error: Violation of UNIQUE KEY constraint 'UQ__#RemoteS__E75FB5011B4D2163'. Cannot insert duplicate key in object 'dbo.#RemoteServerFileEvidence'. The duplicate key value is (101, <NULL>, 95, 3).
Found 0 remote file usage records
Found 0 remote file user access records
2016-07-20 00:16:19,500 [INFO ] All retries have been attempted for Writer 'RemoteApplicationsByFileEvidenceTempTables'
2016-07-20 00:16:19,500 [ERROR] System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'UQ__#RemoteS__E75FB5011B4D2163'. Cannot insert duplicate key in object 'dbo.#RemoteServerFileEvidence'. The duplicate key value is (101, <NULL>, 95, 3).
Found 0 remote file usage records
Found 0 remote file user access records
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 asyncWrite)
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:574b4e16-5f4a-4063-835b-96e6fa892b53
Error Number:2627,State:2,Class:14

Cause

This issue has been identified as Bug #FNMS-34747.

This will arise when a customer has the following conditions met

• Using XenApp 7.0+
• Still publishing file based applications instead of App-V packages
• Publishing the same file based application across multiple desktop groups

Resolution

Please add VDIGroupUUID column to the unique clustered index of #RemoteServerFileEvidence table, it is defined in "C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\InstalledSoftware.xml" line 567.

<Writer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
Name="RemoteApplicationsByFileEvidenceTempTables"
Order="155"
Retries="1">
<![CDATA[

IF OBJECT_ID('tempdb..#RemoteServerFileEvidence') IS NOT NULL
DROP TABLE #RemoteServerFileEvidence

CREATE TABLE #RemoteServerFileEvidence (
ComplianceConnectionID INT,
ExternalServerID BIGINT,
VDIGroupUUID UNIQUEIDENTIFIER,
ExternalFileID BIGINT,
AccessModeID INT,
UNIQUE CLUSTERED(ComplianceConnectionID, ExternalServerID, VDIGroupUUID, ExternalFileID, AccessModeID)
)