How to purge Contact Expert databases
Contact Expert implementation involves a fairly large number of tests to validate the environmental systems (e.g. telephony, network) as well as the software features. This results in the CE statistical reports and the relevant databases getting 'contaminated' with these test contacts (voice calls, emails, chat sessions and histories).
Customers might also decide to purge the CE databases of all existing contact history and statistical data for any reason.
This article describes the process to clean the CE databases of all previous contacts as well as all the statistical data.
Please note that this script will remove any and all traces of any previous contact interactions performed in Contact Expert!
The database purging operation requires a database administrator access to the Microsoft SQL Server database system having all the necessary permissions to delete records, truncate tables and execute stored procedures.
Depending on the 'age' and usage pattern of the system this script might have to deal with a large number of records – this contributes to the amount of time it takes for it to finish. If you 'cleanse' the database of a heavily used system, this will take a lot of time.
Step-by-step guide
Copy the following Microsoft T-SQL database script:
CE Database Contact Cleanup
DECLARE @campaign_cur CURSOR DECLARE @campaign_id INT DECLARE @crds_tname NVARCHAR(128) DECLARE @sql NVARCHAR(512) USE [ACESystem] SET @campaign_cur = CURSOR FOR SELECT camp_id FROM campaigns OPEN @campaign_cur FETCH NEXT FROM @campaign_cur INTO @campaign_id WHILE (@@fetch_status = 0) BEGIN SET @crds_tname = 'CRDS_' + CONVERT(NVARCHAR(16), @campaign_id) SET @sql = N'TRUNCATE TABLE ' + @crds_tname + '' EXECUTE sp_executesql @sql FETCH NEXT FROM @campaign_cur INTO @campaign_id END CLOSE @campaign_cur DEALLOCATE @campaign_cur TRUNCATE TABLE history TRUNCATE TABLE history_archive TRUNCATE TABLE sessions TRUNCATE TABLE contactnum DELETE FROM contacts USE [ACEReport] TRUNCATE TABLE callstat TRUNCATE TABLE callstat_archive TRUNCATE TABLE agentstat TRUNCATE TABLE agentstat_archiveShut down CE – no agent should be logged in and no contact should be arriving to the system while the database is cleaned.
- Back up the CE databases.
- Log on to the CE Database Host or any computer from where you can initiate a database connection to the CE Databases.
- Launch Microsoft SQL Server Management Studio with a user account having granted the required permissions.
- Open a new Query window, paste the script you downloaded from this page, select the whole script. Make sure the query window contains the script text only, nothing else.
- Run the script (click Execute). The script should run without any errors.
- If the cleaning operation was successful, restart Contact Expert.
- If the operation failed, you might have to roll back the original databases and visit the How to initiate a Contact Expert incident investigation article.
Note
Do not run this script on a production system while it is in operation! You should stop all CE operations while the script is running!