'Upgrade statement failed', while upgrading to CMDB and Asset 6.5

Article:TECH34218  |  Created: 2007-09-20  |  Updated: 2012-03-16  |  Article URL http://www.symantec.com/docs/TECH34218
Article Type
Technical Solution


Issue



While upgrading to CMDB Solution 6.5 and Asset Management Solution 6.5, database timeouts occur while the spConvertRAHistToSnapshotHist step is running, or the following error is logged:

Module: AltirisNativeHelper.dll
Source: Altiris.AssetContractCommon.GeneralUtilities.ExecuteSqlNodes
Description: Upgrade statement failed. ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed. SQL: 'if(object_id('dbo.spSkipAsset65HistoryUpgrade') is null)
  begin
   declare
   @rowcount1 int,
   @rowcount2 int
   
  
   --In here i need to call the stored proc to convert the the RA history information for ownership
   --to the dataclass history information      
   if ((object_id('dbo.InvHist_Ownership_Details') is not null) and (object_id('dbo.InvHist_Cost_Center_Ownership') is not null) )
   Begin
    select top 1 _ResourceGuid from InvHist_Ownership_Details
    set @rowcount1 =@@ROWCOUNT
    
    select top 1 _ResourceGuid from InvHist_Cost_Center_Ownership
    set @rowcount2 = @@ROWCOUNT
       
    if (@rowcount1 = 0 and @rowcount2 = 0)
    begin
     --their needs to be 2 separate calls so that the percentage that gets assigned out is appropriate depending on the resource association
     --this resolves a problem of having 2 user owners and one CostCenter owner what it was doing was assigning 33.33 percent to all the owners of the asset
     --which is incorrect it should assign 50 50 first and then 100% the reason this was happening was because it was running under the same stored proc
     exec spConvertRAHistToSnapshotHist 'ed35a8d1-bf60-4771-9dde-092c146c485a, 1466e770-4413-4517-a89d-6599b8a7f144' -- asset-user, asset-department
     exec spConvertRAHistToSnapshotHist '9bc22d17-c0cf-45d5-9a8f-d62bbafd955d' --asset-costCenter
    end
   end
  end'


Environment



CMDB Solution 6.5
Asset Management Solution 6.5


Cause



This can be caused by having a very large number of entries in the ResourceAssociationHistoryDelta table what match the WHERE clause of the following sql statement:

select count(*) from ResourceAssociationHistoryDelta
where ResourceAssociationTypeGuid in (
   'ED35A8D1-BF60-4771-9DDE-092C146C485A',         -- Asset User Owners
   '1466e770-4413-4517-a89d-6599b8a7f144',         -- Asset Department Owners
   '9bc22d17-c0cf-45d5-9a8f-d62bbafd955d',         -- Cost Center
   '5cf17724-8f75-4505-8898-7b42d8cd62da',         -- Software Purchase User
   '0DA075AC-6FCA-411C-AEEC-AE1003FA93E6',         -- Software Purchase Department
   '338A1ECF-983D-49a9-B19E-FAE972AC1A3F'          -- Cost Center (Software Purchase)
)


Solution



There is a flag you can set in the database before the upgrade that disables the running of the stored procedure spConvertRAHistToSnapshotHist. This allows you to skip it during upgrade, and then run it standalone after the upgrade.

  1. Create an object in the database called “spSkipAsset65HistoryUpgrade”, using the following SQL script:

    CREATE PROCEDURE spSkipAsset65HistoryUpgrade AS

    BEGIN
          PRINT 'a'
    END
     
  2. If the upgrade has not been performed yet, begin the upgrade; if the upgrade was performed and failed, first repair CMDB Solution 6.5 as per KB35204, then Asset Management Solution 6.5 as per KB38061.
     
  3. Delete the spSkipAsset65HistoryUpgrade procedure with the following SQL statement:

    DROP PROCEDURE spSkipAsset65HistoryUpgrade
     
  4. Run the following SQL script:
     

EXEC spConvertRAHistToSnapshotHist 'ED35A8D1-BF60-4771-9DDE-092C146C485A, 1466e770-4413-4517-a89d-6599b8a7f144' --asset-user, asset-department

EXEC spConvertRAHistToSnapshotHist '9bc22d17-c0cf-45d5-9a8f-d62bbafd955d' --asset costcenter

EXEC spConvertRAHistToSnapshotHist '5cf17724-8f75-4505-8898-7b42d8cd62da, 0DA075AC-6FCA-411C-AEEC-AE1003FA93E6' --Software Purchase User, Software Purchase Department

EXEC spConvertRAHistToSnapshotHist '338A1ECF-983D-49a9-B19E-FAE972AC1A3F' --Cost Center (Software Purchase)

 




Legacy ID



37874


Article URL http://www.symantec.com/docs/TECH34218


Terms of use for this information are found in Legal Notices