Repair, Reconfigure and or AeXConfig /configureall modifies spLockResource to pre-7.5 SP1 HF2

Products

• IT Management Suite

Issue/Introduction

When reconfiguring the database, repairing the Platform and/or running "AeXConfig /configureall" errors appear in the NS logs.
Alternately if you are looking at this article before reconfiguring the database, repairing the Platform and/or  running "AexConfig /configureall", only Scenario 1 under the Solution section in this article is required.

There are many issues that come from running a Reconfigure of the database, Repair of the Platform and or  AexConfig /configureall, some of the more critical are below:

• Patch Management solution is missing tables, views and stored procedures like Inv_Software_Update causing it to be broken in many ways
• Asset and CMDB are marked as uninstalled and will not be visible in the console.
• Unspecified Dataloader SQL Exception encountered for inventory data class 'AeX Push Status' (c88f1209-18ed-436c-b245-06c77557d364). (SQL Exception Number: 8144)
• Procedure or function spLockResource has too many arguments specified.
• SQL Exception details: code=8144, procedure=spLockResource
• Failed to load inventory.
• Procedure or function spLockResource has too many arguments specified

Cause

When reconfiguring the database, repairing the Platform and/or running "aexconfig /configureall", the general behavior is that aexconfig will run the original stored procedures contained in the config files (for example, if you are in 7.5 SP1 HF2, aexconfig runs the original config files for 7.5 SP1 and then it runs the new ones for HF2). In this case spLockResource is reverted to its original version causing the errors above but if the process is left running, it should update it to the newer one. Since the new HF2 or later passes more parameters to this stored procedure than the originally expected with 7.5 SP1, it causes the repair to have unexpected behaviors with some Solutions like Asset, CMDB, and Patch (missing menus, dataclasses, etc).

Resolution

There are two solutions, the first is for times when you are planning on running a Reconfigure of the database, Repair of the Platform and/or running "AexConfig /configureall" and want to avoid problems the second is for scenarios where it has already been run.

Scenario one

Update the config files (CoreDatabase.config and NS_Update_Database.config) before running "/configureall"

1. Open <Install Dir>:\Program Files\Altiris\Notification Server\Config\NS_Hotfix_SqlObjects.config and locate the section that creates the spLockResource stored procedure or use the attached spLockResource.sql code.
2. Highlight the syntax to create the entire stored procedure and copy it to the clipboard
3. Open  <Install Dir>:\Program Files\Altiris\Notification Server\Config\NS_Update_Database.config and locate the section that creates the spLockResource stored procedure (find the section on those config files where starts with CREATE PROCEDURE [dbo].[spLockResource]and replace it with the updated one).
4. Highlight the syntax to create the entire stored procedure and replace it with what is in the clipboard from the NS_Hotfix_SqlObjects.config file
5. Open  <Install Dir>:\Program Files\Altiris\Notification Server\Config\CoreDatabase.config and locate the section that creates the spLockResource stored procedure (find the section on those config files where starts with CREATE PROCEDURE [dbo].[spLockResource]and replace it with the updated one).
6. Highlight the syntax to create the entire stored procedure and replace it with what is in the clipboard from the NS_Hotfix_SqlObjects.config file
7. Run "AexConfig /configureall"

Scenario two

"/Configureall" has already been run and you need to recover:

1. Follow steps 1 to 6 in Scenario One above
2. If Asset and CMDB solutions are installed
   1. Open the Symantec Installation Manager
   2. Locate Altiris Asset Management Solution 7.5 SP1 and select Uninstall
   3. Wait for the process to complete and click Finish
   4. Locate Altiris CMDB Solution 7.5 SP1 and select Uninstall
   5. Wait for the process to complete and click Finish
3. If Patch Management is installed
   1. Run through the steps in TECH174212 or run the attached Inv_Software_Update(Create Script).sql to rebuild the missing table
   2. Run through the steps in HOWTO47868 to reconfigure patch as it often has issues with "aexconfig /configureall"

 

Applies To

 SMP 7.5 SP1 HotFix2 and later