Unicode Version of Mail Archive

Version 9

    Verified Product Versions

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

    Question

    Unicode Version of Mail Archive scripts

    Answer

    See attached scripts.

     

    Updated

    The latest version uses set operations to archive the emails to improve performance and reduce locking issues.

     

     

     

    Information

    Please note that the sp_MMArchive script may require tps_body to be changed from an nvchar to a vchar when running SQL 2000 **

    First run message-tables.sql against your ServiceDesk database in SQL Query Analyzer.

     

    This will create 2 tables called:

    tps_user_message_archive and tps_user_message_recipient_archive

     

    Next run rogue-entries.sql against your ServiceDesk database in SQL Query Analyzer.

    This will remove any incorrect entries from the tps_user_message table where there is no entry in the tps_user_message_recipient table.

     

    Next you will need to run Mail Manager Archive SP.sql against your ServiceDesk database in SQL Query Analyzer.

    This will archive all sent messages.

     

    You will now need to ensure that most of the entries in the tps_user_message and tps_user_message_recipient tables have been moved to the tps_user_message_archive and tps_user_message_recipient_archive tables.

     

    Next you will need to run MMArchive.sql against your ServiceDesk database in SQL Query Analyzer.

     

    Then schedule the stored procedure SP_MMArchive to run once each day after the last backup of the day.

     

     

     

     

     

      Environment

    ITBM Console prior to version 731

     

    ***********************************************************

    This script be run by your database administrator and tested against a test copy of your database before applying live. When you apply to the live database, please ensure that no-one else is logged in and that a backup of the database has been taken. In the event of any problems you should restore from the backup copy.

     

    ***********************************************************

    UPDATE:

    From 731 onwards you can use a scheduled bulk action to  clear the entries in the message recipient table instead.  More details  on how to do this can be found within the 73NewFeatures guide under  Clearing the Message Recipient table within the Changes in 731 section.