"Check Software Update Package Integrity" Task Fails Due to SQL Constraint Error on Temp Table

Products

• IT Management Suite

Issue/Introduction

The "Check Software Update Package Integrity" task (under  Manage > Jobs and Tasks > System Jobs and Tasks > Software > Patch Management), which is part of the Software Management Solution (Patch Management), fails intermittently when executed manually ("Start Now") or as a scheduled task.

This failure leads to a buildup of old and unused software update packages on Package Servers, consuming disk space and causing operational inefficiencies.

The Altiris Log Viewer on the SMP Server (Symantec Management Platform) reports an exception that points to a SQL constraint error related to a temporary table within a stored procedure.

Simplified NS log entry:

An exception was thrown while checking patch packages.

Item save failed: 'Windows8.1-KB3064209-x86.msu' (827998f7-6304-6273-654e-4581a1453a2f)
......

Database context invalidated by inner exception
......

There is already an object named 'PK_AdvResTargetsAndFilters' in the database.
Could not create constraint or index. See previous errors.
......

SQL Exception details: code=2714, class=16, hr=0x80131904, line=44, procedure=spGetAutoAssignInfoForPackage

 

Error Snippet from Logs

The logs show an exception similar to:

There is already an object named 'PK_AdvResTargetsAndFilters' in the database.
Could not create constraint or index. See previous errors.

Environment

IT Management Suite (ITMS) 8.7.3 and 8.8 RTM
Microsoft SQL Server, particularly versions 2016 and later (such as SQL 2022 CU21) which utilize aggressive temporary table caching

Cause

The root cause of this defect is related to how Microsoft SQL Server handles the creation of temporary tables (#temp tables) within stored procedures, specifically the spGetAutoAssignInfoForPackage stored procedure used by the Patch Management Solution.

1. Temporary Table Caching: SQL Server 2016 and later versions employ an aggressive temporary table caching mechanism to improve performance by caching the structure (schema) of frequently created temporary tables.

2. Named Constraints Conflict: The temporary table being created within the stored procedure, #AdvResTargetsAndFilters, was defined with a named unique constraint (PK_AdvResTargetsAndFilters).

3. The Defect: When the stored procedure runs simultaneously or frequently in rapid succession (creating and dropping the temporary table), the SQL Server caching mechanism can lead to a conflict where it tries to re-create the named constraint when it already exists in the cache, resulting in the "There is already an object named..." error and the task failure. Named constraints must be unique, and this caching behavior interferes with that uniqueness for temporary tables across different sessions.

Resolution

This issue is addressed under our next ITMS 8.8.1 release.

 

A Point Fix (PF) is available that modifies the stored procedure to define the temporary table's constraint without a name.

Apply the cumulative Point Fix (PF) relevant to your ITMS version, or a later version, to resolve the defect:

ITMS Version	Required Point Fix (or later)	Download Source
ITMS 8.7.3	SMA_SMP_8_7_3_PF_v11	CUMULATIVE POST ITMS 8.7.3 POINT FIXES
ITMS 8.8	SMA_SMP_8_8_PF_v6	CUMULATIVE POST ITMS 8.8 RTM(GA) POINT FIXES

Note: The Point Fix modifies the stored procedure spGetAutoAssignInfoForPackage to use an unnamed constraint for the temporary table #AdvResTargetsAndFilters, which prevents the named constraint conflict during SQL Server's temporary table caching.