Known Issue: EvidenceAlerts import writer step may fail with error when using SQL Server 2016: INSERT EXEC failed because the stored procedure altered the schema of the target table. (IOJ-2101652)

[#1]

Symptoms

The writers phase of the inventory import process intermittently fails with an error like the following when the FlexNet Manager Suite compliance database is hosted on a SQL Server 2016 instance:

Failed to execute Writer 'EvidenceAlerts' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\InstalledSoftware.xml, at step line 39 Error: INSERT EXEC failed because the stored procedure altered the schema of the target table.

Details

Details similar to the following appear in the import log when this issue occurs:

2019-01-16 02:02:07,598 [INFO ] Failed to execute Writer 'EvidenceAlerts' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\InstalledSoftware.xml, at step line 39 Error: INSERT EXEC failed because the stored procedure altered the schema of the target table.
2019-01-16 02:02:07,598 [INFO ] All retries have been attempted for Writer 'EvidenceAlerts'
2019-01-16 02:02:07,692 [ERROR] System.Data.SqlClient.SqlException (0x80131904): INSERT EXEC failed because the stored procedure altered the schema of the target table. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
[...]

This failure is caused by the issue described in the following Microsoft article: INSERT EXEC fails because the stored procedure altered the schema of the target table error in SQL Server 2016.

Resolution

This issue is fixed in SQL Server 2016 Service Pack 3, so the issue can be avoided by upgrading to that service pack or a newer version of SQL Server.

An alternate mitigation strategy is to make the following configuration change to SQL Server as described in the Microsoft article:

Increase the size of the Query Data Store. This will reduce the frequency or likelihood of the Query Data Store clearing out the plan and entering READ_ONLY operating mode.

Fix details

The queries used in the FlexNet Manager Suite import process have been updated to minimize the frequency with which the issue occurs. However these changes do not guarantee that failures will not continue to intermittently occur. Upgrade SQL Server as described above to be confident that future imports will not continue to be affected by this issue.

Fix status

This issue has been fixed in the following FlexNet Manager Suite release: 2021 R1 (On Premises)

Other information

Affected components: Inventory import (read/write/export)

Master issue ID: IOJ-2101652

Also known as: FNML-69715