Problem / Solution: TTL fails if a privilege is created in both Test and Live environments

Version 8

    Verified Product Versions

    Service Desk 7.7.xService Desk 7.8.xService Desk 2016.xAsset Manager 2016.xAsset Manager 2017.xService Desk 2017.x


    Test to Live from 2016.1 onward


    Requires Access To:

    • Test to Live application
    • Database Server


    Error message:

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tps_privilege' and the index name 'index$priv$coll_item'.



    The above error message appears in the Test To Live logs after failing the synchronisation.

    This has been identified as the problem 6701 - TTL fails if a privilege is created both in Test and Live before the synchronisation, please log a case on our support portal with this problem as a reference if you encounter this issue.

    The issue has been fixed in TTL of 2017.3.



    This occurs if you modify manually a privilege in live and in test, and if this privilege was never set before.


    In the back end:

    As described at the end of this article How To: Understand the structure of privileges in the Service Desk database, a new entry is created in the tps_privilege table if a privilege was set up for the first time.


    If such a privilege is set up in both test and live environment, this will create twice the same entry, but with two different unique identifiers (tps_guid) as those are randomly generated. As this new entry's identifier doesn't exist in Live, Test To Live attempts to transfer this record but the SQL unique constraint based on two other columns (tps_item_guid, tps_collection_guid) is violated.


    Solution / Workaround:

    As a workaround in the meantime, the attached script can be run to re-synchronise the identifiers in Test with the one in Live for the tps_privilege table. This will avoid Test To Live to transfer the faulty records.


    1. Perform a database backup of your Live and your Test environment
    2. Open the script in SQL management studio
    3. Replace the values of the two variable @TESTDB and @LIVEDB by the relevant database names or locations
      The two databases must be accessible from the same server to be used in this script
    4. Run the script
    5. Run Test to Live
      If you encounter an issue, this might be related to another problem. Restore your database backups and use the support portal to fix the issue if necessary.
    6. Once the transfer successful, immediately copy the Live database over the Test database to ensure a full synchronisation


    This script was created for 2016.x in order to temporarily fix the problem 6701 and is not supported, you may want to have a database backup before running it.