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

  • 7001162
  • 14-Aug-2008
  • 27-Apr-2012

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.

Feedback service temporarily unavailable. For content questions or problems, please contact Support.