How to delete SQL Server whole or partial historical call archive and re-import it from existing "Storico" file
Applies to:
Imagicle Call Analytics Standard/Enterprise
Description:
How to remove calls from SQL Server database and reimport them from "Storico" text file
Solution: UC Suite starting from 2021.3.1
1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager
2. open Microsoft SQL Manager Studio (make a backup copy of the database)
3. Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.
Please keep in mind that dates are based on UTC Time Zone.
/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */
-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'
-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-01 00:00:00.000'
-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000
----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)
SET ROWCOUNT @recordsPerChunk
RAISERROR('Deleting [BibCalls] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
SELECT @recordsNumber = COUNT(*) FROM [BibCalls] WHERE [UtcStartDateTime] >= @startDate AND [UtcStartDateTime] < @endDate
RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
BEGIN TRANSACTION
DELETE FROM [BibCalls] WHERE [UtcStartDateTime] >= @startDate AND [UtcStartDateTime] < @endDate
COMMIT TRANSACTION
END
RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
BEGIN TRANSACTION
DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
COMMIT TRANSACTION
END
SET ROWCOUNT 0
4. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard
5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings
6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls
Solution: UC Suite starting from 2020.3.1 up to 2021.1.1
1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager
2. open Microsoft SQL Manager Studio (make a backup copy of the database)
3. Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.
/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */
-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'
-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-01 00:00:00.000'
-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000
----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)
SET ROWCOUNT @recordsPerChunk
RAISERROR('Deleting [BibCalls] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
SELECT @recordsNumber = COUNT(*) FROM [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
BEGIN TRANSACTION
DELETE FROM [BibCalls] WHERE [StartDate] >= @startDate AND [StartDate] < @endDate
COMMIT TRANSACTION
END
RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
BEGIN TRANSACTION
DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
COMMIT TRANSACTION
END
SET ROWCOUNT 0
4. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard
5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings
6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls
Solution: UC Suite previous 2020.3.1
1. Stop Blue's Service Host in Billing|Administrative pages|Service Manager
2. open Microsoft SQL Manager Studio (make a backup copy of the database)
3. Run "New Query" and copy the following query, by changing the @startDate and @endDate according to the period you wish to delete. If you need to fully erase historical archive, please set @startDate to year 2000 and @endDate to year 2030.
/* WARNING: BEFORE YOU RUN THIS SCRIPT TAKE A BACKUP OF THE DATABASE! */
-- Declare the date after which the calls will be canceled
-- The start date (included) of the deletion
DECLARE @startDate DATETIME = '2020-01-01 00:00:00.000'
-- The end date (excluded) of the deletion
DECLARE @endDate DATETIME = '2020-02-29 00:00:00.000'
-- Declare the site to be canceled
DECLARE @sito VARCHAR(50)
set @sito = 'SITE'
-- Number of records that must be deleted in each chunk
DECLARE @recordsPerChunk INT = 1000
----- DO NOT EDIT BELOW THIS LINE -----
DECLARE @recordsNumber INT
DECLARE @startDateString VARCHAR(50) = CONVERT(VARCHAR(50), @startDate, 120)
DECLARE @endDateString VARCHAR(50) = CONVERT(VARCHAR(50), @endDate, 120)
SET ROWCOUNT @recordsPerChunk
RAISERROR('Deleting [ChiamateInterne] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
SELECT @recordsNumber = COUNT(*) FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
BEGIN TRANSACTION
DELETE FROM [ChiamateInterne] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
COMMIT TRANSACTION
END
RAISERROR('Deleting [Chiamate] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
SELECT @recordsNumber = COUNT(*) FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
BEGIN TRANSACTION
DELETE FROM [Chiamate] WHERE [dData] >= @startDate AND [dData] < @endDate and sSito = @sito
COMMIT TRANSACTION
END
RAISERROR('Deleting [Scarti] records with date greater or equal than %s and less than %s', 0, 1, @startDateString, @endDateString) WITH NOWAIT
SET @recordsNumber = 1
WHILE @recordsNumber > 0
BEGIN
SELECT @recordsNumber = COUNT(*) FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
RAISERROR('Number of records to delete: %d', 0, 1, @recordsNumber) WITH NOWAIT
BEGIN TRANSACTION
DELETE FROM [Scarti] WHERE [dData] >= @startDate AND [dData] < @endDate
COMMIT TRANSACTION
END
SET ROWCOUNT 0
( change the dates of the above query according to the period you wish to delete)
4. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro, from file StoricoSITE.txt, select all the lines you need to reimport and hit CTRL+C on the keyboard
5. in folder \\StonevoiceAS\Apps\BillyBlues\Engine\BluesRecPro open bluesrecsite.txt and use CTRL+V to paste in the previously copied strings
6. Start Blue's Service Host in Billing|Administrative pages|Service Manager and wait until miner has finished importing all calls
|
||
This article was: |
Prev | Next | |
How to modify attachment size limit on scheduled reports | Show calls currency using 3 English letters |