Upgrade from ZCM 10.0.3 to ZCM 10.1 fails processing t-assetmanagement-10-create-tables-multiline.sql file

  • Document ID:7001162
  • Creation Date:14-Aug-2008
  • Modified Date:27-Apr-2012
    • Micro Focus Products:
      ZENworks Configuration Management

Environment

Novell ZENworks 10 Configuration Management

Situation

Upgrade from ZCM 10.0.3 to ZCM 10.1 fails processing t-assetmanagement-10-create-tables-multiline.sql file.
 
ERROR: One or more serious errors occurred during the schema update.
 
 
More details of the issue are viewable by changing the "Information" drop down menu item in the ZENworks_Install log to "Debug".
 
The following message will then be seen:

Processing localization-updater-drop.sql

Processing localization-updater-multiline.sql

...(listing of a some additional .sql files)

Processing t-assetmanagement-10-create-tables-multiline.sql

SEVERE: Error while upgrading the Database

java.sql.SQLException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LC_PurchasedProduct' and the index name 'UQ_PrchProd_Prod_License'. The duplicate key value is (Microsoft, Publisher, 2007, Full License, 1).

 

Resolution

1. Revert the database back to its working state.

2. Copy the 10.1. media to a location where it may be edited and executed

3. Open up "Upgrade\Disk1\InstData\sql\sql-server\upgrade\pmr-r1\t-assetmanagement-10-create-tables-multiline.sql"with a simple text editor

4. Search for "LC_License", "LC_ProductStandards", and "LC_PurchasedProduct" and remove the lines ALTER TABLE _tablename_ WITH NOCHECK until the next "GO" statement by adding "--" at the beginning of the line as follows:

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LC_License]') AND name = N'UQ_License_Prod_Account')

ALTER TABLE [dbo].[LC_License] DROP CONSTRAINT [UQ_License_Prod_Account]

GO

--ALTER TABLE LC_License WITH NOCHECK ADD

-- CONSTRAINT UQ_License_Prod_Account UNIQUE

-- (

-- Manufacturer,

-- LicenseName,

-- Version

-- )

--GO

-- can't add these next two in Sybase due to nullable columns

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LC_ProductStandards]') AND name = N'UQ_LC_ProductStandards')

ALTER TABLE [dbo].[LC_ProductStandards] DROP CONSTRAINT [UQ_LC_ProductStandards]

GO

--ALTER TABLE LC_ProductStandards WITH NOCHECK ADD

-- CONSTRAINT UQ_LC_ProductStandards UNIQUE

-- (

-- ProductOID,

-- GroupName

-- )

--GO

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LC_PurchasedProduct]') AND name = N'UQ_PrchProd_Prod_License')

ALTER TABLE [dbo].[LC_PurchasedProduct] DROP CONSTRAINT [UQ_PrchProd_Prod_License]

GO

-- ALTER TABLE LC_PurchasedProduct WITH NOCHECK ADD

-- CONSTRAINT UQ_PrchProd_Prod_License UNIQUE

-- (

-- Manufacturer,

-- Product,

-- Version,

-- Type,

-- LicenseQty

-- )

-- GO

5. Save the file and re-run the upgrade installer.

Additional Information

All of the SQL files were executed successfully up until "t-assetmanagement-10-create-tables-multiline.sql". This problem may be caused by the "Purchase Record Importer" feature of Asset Management used prior to the 10.1.0 upgrade.