Full Recon failed after upgrade to 2019 R2
We have installed FNMS 2019 R2 On-premises and Full Recon was failed after upgrade to 2019 R2.
Case also logged but required help on priority as recon was failed from 10th Dec.
Case Number: 01956769
Failed to import inventory devices with error message 'Conversion failed when converting the nvarchar value '188.8.131.52.0' to data type int.'
It takes more than 4 hours at step InstalledSoftwareAlerts
2019-12-13 11:43:18,833 [INFO ] ApplicationAlerts
2019-12-13 11:43:59,655 [INFO ] Successfully processed in 40 seconds
2019-12-13 11:43:59,655 [INFO ] EvidenceAlerts
2019-12-13 16:42:12,024 [INFO ] Successfully processed in 4 hours, 58 minutes, 12 seconds
2019-12-13 16:42:12,024 [INFO ] InstalledSoftwareAlerts
2019-12-13 16:42:12,330 [INFO ] Failed to execute Writer 'InstalledSoftwareAlerts' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\InstalledSoftware.xml, at step line 40
Error: Conversion failed when converting the nvarchar value '184.108.40.206.0' to data type int.
2019-12-13 16:42:12,330 [INFO ] All retries have been attempted for Writer 'InstalledSoftwareAlerts'
2019-12-13 16:42:12,361 [ERROR] System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '220.127.116.11.0' to data type int.
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)
2019-12-13 16:42:12,815 [INFO ] Completed with error in 0 seconds
2019-12-13 16:42:12,815 [INFO ] Released application lock Writer_Source_MatchARL_ALL
2019-12-13 16:42:12,831 [INFO ] Released application lock ManageSoftComplianceImporter_Exclusive
2019-12-13 16:42:12,877 [INFO ] Time: Friday, December 13, 2019 4:42:12 PM
2019-12-13 16:42:12,877 [INFO ] Total import time: 6 hours, 18 minutes, 58 seconds
2019-12-13 16:42:12,893 [INFO ] 11 source data warnings
2019-12-13 16:42:12,893 [INFO ] 1 errors, 0 warnings
We have installed MS SQL 2016 SP2 so previously SQL compatibility level was set 20 (SQL Server 2016 (130)) then we set to SQL Server 2014 (120) and also check with SQL Server 2012 (110)
But still Full Recon was fail. Prior to upgrade Full Recon was completed before 2 hrs completes and after upgrade it failed.
I have attached snapshot of SQL Execution plan.
This thread has been automatically locked due to inactivity.
To continue the discussion, please start a new thread.
The import step executes the stored procedure "ProcessAlertsForInstalledSoftware". If I was in your position, I would take the SQL code from the SP and try to run it manually. It does not seem to refer to any temp tables from the rest of the writer. That way, you might be able to identify what data exaclty is causing the issue.
Also, if this happened directly after the upgrade, pleas emake sure to check all schema versions in the "DatabaseConfiguration" tables of all FNMS databases. And plase review the DB migration logs as well.
A KB article of this product defect has been created
The KB contains a patch for 2020 R1 and former version user.