"Cannot insert duplicate key in object "dbo.tps_licence_audit_item" " error during admin tasks

Version 9

    Environment:  Applies to Service Desk 7.3 and earlier versions

    (Fixed in 7.3.1 and the tps_licence_audit_item table does not exist from 7.5 onwards)

    Question

    You see the error message "Cannot insert duplicate key in object "dbo.tps_licence_audit_item" " when performing admin tasks with users or changing group in Console.

     

    Error message in German Environment:

     

    Eine Zeile mit doppeltem Schlüssel kann in das 'dbo.tps_licence_audit_item'-Objekt mit dem eindeutigen 'index$tps_lic_audit_item$t'-Index nicht eingefügt werden.

    Die Anweisung wurde beendet.

    Answer

    Run the following SQL Server statements against your ITBM database to fix an index on the tps_licence_audit_item table:

     

    DROP INDEX index$tps_lic_audit_item$t ON tps_licence_audit_item

     

    CREATE NONCLUSTERED INDEX index$tps_lic_audit_item$t ON tps_licence_audit_item (tps_title ASC)

    WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

     

    If possible please try this first on a test system and take a full backup of your database before running the statements.

     

    For the Oracle version see below:

     

    DROP INDEX index$tps_lic_audit_item$t ON tps_licence_audit_item;

    CREATE INDEX index$tps_lic_audit_item$t ON tps_licence_audit_item(tps_title);

     

    Manual Fix

    If you experience any issues running the scripts above or they do not fix the issue, here are some steps to manually alter the database:

     

    SQL Server 2000

    1. Open SQL Server Enterprise Manager.

    2. Expand the tree to Databases -> Your Service Desk Database and select Tables.

    3. From the list of tables on the right locate tps_licence_audit_item.

    4. Right-click the entry in the table list and select All Tasks -> Manage Indexes...

    5. Select the index$tps_lc_audit_item$t index and press the Edit... button.

    6. On the dialog that appears uncheck the "Unique Values" checkbox.

    7. Press OK to save the change then Close to close the Indexes dialog.

     

    SQL Server 2005

    1. Open SQL Server Management Studio.

    2. Expand the tree to Databases -> Your Service Desk Database -> Tables -> dbo.tps_licence_audit_item -> Indexes.

    3. Right-click on index$tps_lc_audit_item$t and select Properties from the menu.

    4. On the diaog that appears uncheck the "Unique" checkbox.

    5. Press OK to save the change.

     

    Oracle

    Manual steps are not possible to alter the index in this way, you must remove and re-create it as the SQL statements do above.

     

    Any customer that comes across this issue please still raise with your support provider so we can link you to the problem record that exists for the root cause of this error.  Quote problem reference 3302 when communicating with us.