'Upgrade statement failed', while upgrading to CMDB and Asset 6.5
Last Updated March 16, 2012
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'
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) )
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.
Create an object in the database called “spSkipAsset65HistoryUpgrade”, using the following SQL script:
CREATE PROCEDURE spSkipAsset65HistoryUpgrade AS
BEGIN PRINT 'a' END
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
Delete the spSkipAsset65HistoryUpgrade procedure with the following SQL statement: