Random Sampling fails with error: value violates the MaxLength limit of this column

Problem

Enterprise Vault (EV) Compliance Accelerator (CA) / Veritas Advanced Surveillance (VAS) Random Sampling processing fails to run and logs the error below. If Guaranteed Sample Searches (GSS) are configured, the GSS processing also fails and GSS are not run.

Error Message

CA/VAS server's Enterprise Vault Event Log Error:

Event ID:  272
Process: Accelerator Service Process
Description:
APP AT - Customer ID: X - Guaranteed Sampling: Error during guaranteed sampling - aborting. System.ArgumentException: Cannot set column 'Author'. The value violates the MaxLength limit of this column.
   at System.Data.DataColumn.CheckMaxLength(DataRow dr)
   at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
   at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean suppressEnsurePropertyChanged, Int32 position, Boolean fireEvent, Exception& deferredException)
   at System.Data.DataTable.InsertRow(DataRow row, Int64 proposedID, Int32 pos, Boolean fireEvent)
   at KVS.Accelerator.Sampling.SampledItemsDS.VaultSampleTypeDataTable.AddVaultSampleTypeRow(String KVSVaultEntryID, String KVSSaveSetId, String Author, String Subject, DateTime MailDate, Int32 NumAttachments, Int32 size, Int32 Direction, Int32 Type, String Recipient, Boolean RecipientTruncated, String TransactionID, String Extension, Int32 ItemLocationID, DateTime ModifiedDate, Int32 PolicyAction, String PolicyXML, String PolicySummary, Boolean PolicySummaryTruncated, Byte EncryptionStatus)
   at KVS.Accelerator.Sampling.GuaranteedSampling.AddVaultSampleTypeRow(VaultSampleTypeDataTable vaultSampleType, CapturedItemRow item)
   at KVS.Accelerator.Sampling.GuaranteedSampling.ProcessCapturedItemsInBatch(CapturedItemsDS items, XmlParserContext context, Int32& iNumberOfItemsProcessed)
   at KVS.Accelerator.Sampling.GuaranteedSampling.Step_1_ProcessCapturedItems()
   at KVS.Accelerator.Sampling.GuaranteedSampling.DoSampling()

V-437-272

Cause

The maximum number of characters allowed in the Author field is 50, by default in the version of the product where this issue was first discovered. When an Author's email address has more than 50 characters, the process that inserts this information into the database fails and causes all Random Sampling processing to stop.

To determine if an author name with 50 or more characters exists in the tblCapturedItems table, open a new query window focused on the CA Customer database in SQL Management Studio and execute the following query:

DECLARE @MaxEmailLength int = 50; -- Edit maximum email length here
IF (OBJECT_ID('tempdb..#tblCapturedItemsTEMP') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #tblCapturedItemsTEMP;';
IF (OBJECT_ID('tempdb..#tblCapturedItemsAuthorName') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #tblCapturedItemsAuthorName;';
SELECT CapturedItemsID, CapturedItemsXML, CapturedDate
INTO #tblCapturedItemsTEMP
FROM tblCapturedItems;
CREATE TABLE #tblCapturedItemsAuthorName (CapturedItemsID bigint, AuthorName nvarchar(max));
DECLARE @CapturedItemsID int;
DECLARE @CapturedItemsXML XML;
DECLARE GetAuthorName CURSOR FOR 
SELECT CapturedItemsID FROM #tblCapturedItemsTEMP;
OPEN GetAuthorName;
FETCH NEXT FROM GetAuthorName INTO @CapturedItemsID;
WHILE @@FETCH_STATUS = 0
BEGIN;
    SELECT @CapturedItemsXML = CapturedItemsXML FROM #tblCapturedItemsTEMP WHERE CapturedItemsID = @CapturedItemsID;
    INSERT INTO #tblCapturedItemsAuthorName
    SELECT @CapturedItemsID, tbl.col.value('.', 'NVARCHAR(MAX)') AS 'AuthorName'
    FROM @CapturedItemsXML.nodes('//@*') AS tbl(col)
    WHERE tbl.col.value('local-name(.)', 'NVARCHAR(MAX)') = 'AuthorName';
    FETCH NEXT FROM GetAuthorName INTO @CapturedItemsID;
END;
CLOSE GetAuthorName;
DEALLOCATE GetAuthorName;
SELECT DISTINCT
CapturedItemsID
, AuthorName
, [@ position] = CHARINDEX('@', AuthorName)
, [# Char left of @] = LEN(LEFT(AuthorName, CHARINDEX('@', AuthorName)-1))
, [# Char right of @] = LEN(RIGHT(AuthorName, CHARINDEX('@', REVERSE(AuthorName))-1))
, [# Char right of including @] = LEN(RIGHT(AuthorName, CHARINDEX('@', REVERSE(AuthorName))))
, [Email length] = LEN(AuthorName)
, [Calculated email length] = (LEN(LEFT(AuthorName, CHARINDEX('@', AuthorName)-1))) + 1 + (LEN(RIGHT(AuthorName, CHARINDEX('@', REVERSE(AuthorName))-1)))
, [Max email length] = @MaxEmailLength
, [Max email length exceeded] = CASE WHEN LEN(AuthorName) > @MaxEmailLength THEN 'Yes' ELSE 'No' END
FROM #tblCapturedItemsAuthorName
WHERE LEN(AuthorName) > @MaxEmailLength
ORDER BY CapturedItemsID, LEN(AuthorName) DESC, AuthorName;
IF (OBJECT_ID('tempdb..#tblCapturedItemsTEMP') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #tblCapturedItemsTEMP;';
IF (OBJECT_ID('tempdb..#tblCapturedItemsAuthorName') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #tblCapturedItemsAuthorName;';

Workaround

A workaround to this issue is to create a Scheduled Search for each Guaranteed Sample Search (GSS) using the GSS as a template to get the remaining criteria.  If GSS is not used, then a Scheduled Search to look for all items using the Yesterday option with no additional criteria can be used.  In these Scheduled Search configurations, the Sampling section will need to be configured to use a sampling percentage lower than the default of 100%.

Solution

Issue relating to 50-character Author limit

There are no plans to address this issue by way of a patch or hotfix in earlier versions of the software at the present time.  However, the issue has been addressed in the revision of the product specified at the end of this article. 
 
Please contact your Veritas Sales representative or the Veritas Sales group for upgrade information including upgrade eligibility to the release containing the resolution for this issue.

This issue relating to the 50-character Author limit has been resolved in the following release:

Enterprise Vault 12.5.2

https://www.veritas.com/support/en_US/downloads/update.UPD334575

Issue relating to 254-character Author limit

The error may still be seen in fixed versions when the Author's email address has more than 254 characters. If this is the case, please follow the steps below to edit any email addresses exceeding 254 characters in tblCapturedItems to less than 254 characters. Note these steps involve editing the affected CapturedItemsID entry in the CA Customer database. The steps should only be performed by persons experienced in running such queries/functions and only after obtaining current SQL backups of the target databases. Please ensure to follow all directions without skipping any steps.

1. Back up the CA Customer database.

2. Determine the following information and edit the variables in the following query accordingly:

2.1. @CapturedItemsID: This is the CapturedItemsID from the above query that lists the email address having more than 254 characters.

2.2. @LongEmail: This is the long email address from the above query having more than 254 characters for the CapturedItemsID.

2.3. @ShortenedEmail: Manually shorten the long email address from the above query having more than 254 characters for the CapturedItemsID as needed to reduce the total length of the email address to 254 characters or less. This can be done by removing characters from the local part of the email address.Note that the determination of which characters to remove is the responsibility of the onsite administrator; Technical Support cannot advise which characters to remove.

3. Execute the edited query against the CA Customer database:

DECLARE
@CapturedItemsID bigint = 1 -- Edit the CapturedItemsID here
, @LongEmail nvarchar(max) = 'long email address' -- Edit the long email address here
, @ShortenedEmail nvarchar(254) = 'shortened email address' -- Edit the shortened to less than 254 characters email address here
UPDATE tblCapturedItems
SET CapturedItemsXML = REPLACE(CAST(CapturedItemsXML AS NVARCHAR(MAX)), @LongEmail, @ShortenedEmail)
WHERE CapturedItemsID = @CapturedItemsID;

4. Allow Random Sampling to occur.

There are no plans to address this issue by way of a patch or hotfix in earlier versions of the software at the present time.  However, the issue has been addressed in the revision of the product specified at the end of this article. 

Please contact your Sales representative or the Sales group for upgrade information including upgrade eligibility to the release containing the resolution for this issue

This issue is fixed in the following release(s), available in the Download Center at https://www.veritas.com/support:
- Enterprise Vault 15.1.2