Service Management Upgrade Error - 'CREATE UNIQUE INDEX statement terminated because a duplicate key was found...'

Products

• CA Service Catalog
• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

When attempting to upgrade Service Management, the upgrade fails and the INSTALL_MDB.LOG file contains errors similar to:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.crwf' and the index name 'crwf_x0'. The duplicate key value is (cr:3068186, 55)

Environment

Service Management 14.x and 17.x
All Supported Operating Systems

Cause

This is caused by some bad data in the MDB database. Basically, there is the same sequence task # for the same ticket #. It is almost like the ticket has multiple references to the same workflow. This needs to be cleaned up. 

What caused the original corruption is not known, but it seems like in older versions of Service Management did not prevent the creation of duplicate sequence #s (possibly through direct modifications to the MDB database).

Resolution

Based on the error above, run the following query against the MDB database:

select * from crwf where object_id='cr:3068186'

Then proceed to delete the duplicate row(s).

NOTE:  There might be more of these duplicate row errors.  If you then rerun the installer and it fails on another duplicate row, you would need to delete that duplicate row as well. In addition, you could try querying the MDB for all records which have the same CR number and more than one identical sequence number.