Reconciliation Error FNMS on-prem 2021 R1
after upgrade to 2021 R1 reconciliation throws an Error:
2021-08-23 04:58:19,161 [INFO ] RecalculateDiscoveredDeviceMembers
2021-08-23 04:58:19,161 [INFO ] Failed to execute Writer 'RecalculateDiscoveredDeviceMembers' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\SyncDiscoveredDevice.xml, at step line 3
Error: Invalid object name '#IncomingDiscoveredDevices'.
2021-08-23 04:58:19,161 [INFO ] All retries have been attempted for Writer 'RecalculateDiscoveredDeviceMembers'
2021-08-23 04:58:19,176 [ERROR] System.Data.SqlClient.SqlException (0x80131904): Invalid object name '#IncomingDiscoveredDevices'.
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 ManageSoft.Compliance.Importer.Logic.XML.Writer.Execute(IExecutionContext context)
at ManageSoft.Compliance.Importer.Logic.ComplianceImporter.ExecuteWriters(ComplianceReader p_ComplianceReader, IExecutionContext context, String singleConnectionIdentifier)
Is that the first error that appears in the log? This looks to me like an error that is likely a consequence of another error that occurred earlier during the import process.
The following error also occurs earlier in this log:
2021-08-23 04:56:02,180 [INFO ] CompileOracleOptionUsageEvidence [...] 2021-08-23 04:56:51,278 [ERROR] Exception in Compile:The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
With that said, there isn't an obvious link between the two errors appearing in the log, so it could be that they are unrelated to two separate matters.
If the failure your encountering is something that continues to occur (i.e. it is not a once-off), and unless somebody else here has some insight, this may be something you may be best served by raising a case with Flexera Support to get some help on.
Hi Matthias & Chris,
The error message that @mschwach reported obviously is related to the temporary SQL table #IncomingDiscoveredDevices not being created correctly. This temporary table should be created in the 'SyncDiscoveredDevice.xml' WRITER module by calling a stored procedure named 'CreateIncomingDiscoveredDeviceTemplate'.
For testing, I would suggest running the following SQL command in SQL Server Management Studio please:
select * from CreateIncomingDiscoveredDeviceTemplate()
The command should return an empty table. There is a statement in 'SyncDiscoveredDevice.xml' where this procedure is used for creating an empty table named #IncomingDiscoveredDevices. If calling the stored procedure succeeds, you should check if any customization has been done to the 'SyncDiscoveredDevice.xml' WRITER file before you upgraded to FNMS 2021 R1.
The error that @ChrisG noticed in line #1210 in the LOG file is a known issue for FNMS 2020 R2 (#02454249 BUG Error related to SQL MERGE). This #02454249 issue does not prevent the completion of the compliance import and it is not related to the "Invalid object name '#IncomingDiscoveredDevices'" error message.
According to the Flexera support, this #02454249 issue has been addressed in FNMS 2021 R1. It is interesting that @mschwach observed this issue after upgrading FNMS to the FNMS 2021 R1 release.
Hello erwinlindemann & Chris,
the statement you (erwinl.) provided does indeed return an empty table with column headers. I'll try to search for any customizations in SysDiscoveredDevice.xml in the past.
regards - Matthias