Error Message

Error Event ID 528:

Log Name:      Veritas Enterprise Vault
Source:        Accelerator Manager 
Event ID:      528
Task Category: None
Level:         Error
Keywords:      Classic
Description:
APP Analytics - AnalyticsConversationAnalyserTask.exe (PID = X) with TransactionId X failed to read items to process, terminating. Exception: Symantec.Accelerator.Analytics.DataAccess.DataAccessException ---> System.Data.SqlClient.SqlException: Invalid object name 'tblIntConversationItems_'.
   at Symantec.EnterpriseVault.DatabaseAccess.EVSqlBase.Do[T](Func`1 action)
   at Symantec.EnterpriseVault.DatabaseAccess.EVSqlDataAdapter.Fill(DataTable dataTable)
   at Symantec.Accelerator.Analytics.DataAccess.DBHelper.FillDataTable(EVSqlCommand cmd, DataTable table)
   --- End of inner exception stack trace ---
   at Symantec.Accelerator.Analytics.DataAccess.DBHelper.FillDataTable(EVSqlCommand cmd, DataTable table)
   at Symantec.Accelerator.Analytics.DataAccess.StoredProcedureAccessorBase.FillDataTable(EVSqlConnection conn, DataTable dataTable)
   at Symantec.Accelerator.Analytics.DataAccess.AnalyticsDataAccessor.FillConversationIndexes(AnalyticsDataset ds)
   at Symantec.Accelerator.Analytics.HierarchyBuilder.BuildConversationHierarchy()

V-437-528

SQL Error Logs entry:

SQL Server Scheduled Job 'RuleEngine Job for Case _' (0x...) - Status: Failed - Invoked on: ... - Message: The job failed. The Job was invoked by Schedule X (RuleEngine JobSchedule for Case _). The last step to run was step 1 (Start).
Executed as user: .... Invalid object name 'tblIntAnalysedItems_'. [SQLSTATE 42S02] (Error 208) Invalid object name 'tblIntAnalysedItems_'. ... [SQLSTATE 42S02] (Error 208). The step failed.

The errors are logged when the Analytics disabling process completes to remove the Analytics objects associated to the case but does not delete the SQL Agent Job associated to the Case's Analytics objects.

To confirm this condition and determine the remediation steps, first edit the following SQL queries for the CaseID listed in the error and execute the queries against the DA Customer database. If there is only one CaseID under investigation, remove the remaining CaseID entries in the query and enclose the one CaseID in parenthesis as indicated in the query. If there are Event Log entries for multiple CaseIDs, add the multiple CaseIDs with each CaseID in parenthesis and each parenthesis-enclosed CaseID separated by a comma, as indicated in the query. Note that if sending this information to Technical Support, please set the output to file under Query | Results To | Results To File.

1. Query 1 - List the Analytics tables for the CaseID. Edit the CaseID as indicated in the query. The output will be in 2 sections. The first section will list information about the CaseID, the second section will list the Analytics tables associated with the CaseID, if any.

SET NOCOUNT ON;
SELECT [Local_Timestamp] = GetDate(), [UTC_Timestamp] = GetUTCDate(), [Customer_Database] = db_name();
IF (OBJECT_ID('tempdb..#AnalyticsCaseID') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #AnalyticsCaseID;';
CREATE TABLE #AnalyticsCaseID (CaseID int);
INSERT INTO #AnalyticsCaseID (CaseID)
VALUES 
-- Enclose each CaseID in parenthesis and list the CaseIDs in a comma-separated list below
-- Note - Multiple INSERT statements are required if the number of SearchIDs exceeds 1000
-- The SQL limit of the VALUES component of the INSERT command has a (SQL) limit of 1000 rows
(1)
, (2)
, (3)
, (4)
, (5)
, (6)
, (7)
, (8)
, (9)
, (10)
-- Enclose each CaseID in parenthesis and list the CaseIDs in a comma-separated list above
;
DECLARE @AnalyticsCaseID int;
DECLARE AnalyticsCasesTables CURSOR FOR
SELECT CaseID
FROM #AnalyticsCaseID
ORDER BY CaseID;
OPEN AnalyticsCasesTables;
FETCH NEXT FROM AnalyticsCasesTables INTO @AnalyticsCaseID;
WHILE @@FETCH_STATUS = 0
BEGIN;
    IF NOT EXISTS (SELECT 1 FROM tblCase WHERE CaseID = @AnalyticsCaseID)
    BEGIN;    
        SELECT [CaseID] = @AnalyticsCaseID
        , [Case Status] = 'Case does not exist';
    END;
    ELSE BEGIN;
        SELECT [CaseID] = @AnalyticsCaseID
        , tc.Name AS 'Case Name'
        , tp.PrincipalName + '(' + tp.PrincipalLogin + ')' AS 'Case Owner'
        , CASE tc.FolderType WHEN 330 THEN 'Case'
        WHEN 331 THEN 'Research Folder'
        WHEN 332 THEN 'Hidden Research Folder (Analytics incapable)'
        ELSE 'Unknown' END AS 'Case Type'
        , tc.StatusID AS 'Case StatusID'
        , ts1.Name AS 'Case Status'
        , tc.AnalyticsStatusID
        , ts2.Name AS 'Current Analytics Status'
        , tc.LastAnalyticsStatusID
        , ts3.Name AS 'Last Analytics Status'
        , tc.AnalyticsStatusText
        FROM tblCase AS tc
        LEFT JOIN tblPrincipal AS tp ON tc.OwnerPrincipalID = tp.PrincipalID
        JOIN tblStatus AS ts1 ON tc.StatusID = ts1.StatusID
        JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
        JOIN tblStatus AS ts3 ON tc.LastAnalyticsStatusID = ts3.StatusID
        WHERE tc.CaseID = @AnalyticsCaseID;
    END;
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE Name LIKE N'%[_]' + CONVERT(nvarchar(25), @AnalyticsCaseID))
    BEGIN;
        SELECT [No Analytics tables exist for CaseID] = @AnalyticsCaseID;
        PRINT '';
    END;
    ELSE BEGIN;
        SELECT *
        FROM sys.tables
        WHERE Name LIKE N'%[_]' + CONVERT(nvarchar(25), @AnalyticsCaseID)
        ORDER BY name;
        PRINT '';
    END;
    FETCH NEXT FROM AnalyticsCasesTables INTO @AnalyticsCaseID;
END;
CLOSE AnalyticsCasesTables;
DEALLOCATE AnalyticsCasesTables;
IF (OBJECT_ID('tempdb..#AnalyticsCaseID') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #AnalyticsCaseID;';
GO

2. Query 2 - Determine if any actions need to be taken if Analytics did not complete the disabling process. The output will be in 2 sections. The first section will list guidance on the steps to be followed, the second section will list the applicable steps to be followed, if any.

SET NOCOUNT ON;
SELECT [Local_Timestamp] = GetDate(), [UTC_Timestamp] = GetUTCDate(), [Customer_Database] = db_name();
IF (OBJECT_ID('tempdb..#AnalyticsCaseID1') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #AnalyticsCaseID1;';
CREATE TABLE #AnalyticsCaseID1 (CaseID int);
INSERT INTO #AnalyticsCaseID1 (CaseID)
VALUES 
-- Enclose each CaseID in parenthesis and list the CaseIDs in a comma-separated list below
-- Note - Multiple INSERT statements are required if the number of SearchIDs exceeds 1000
-- The SQL limit of the VALUES component of the INSERT command has a (SQL) limit of 1000 rows
(1)
, (2)
, (3)
, (4)
, (5)
, (6)
, (7)
, (8)
, (9)
, (10)
-- Enclose each CaseID in parenthesis and list the CaseIDs in a comma-separated list above
;
DECLARE @AnalyticsCaseID1 int, @AnalyticsStatusID1 int, @ActionNeeded bit;
DECLARE AnalyticsCasesTables CURSOR FOR
SELECT CaseID
FROM #AnalyticsCaseID1
ORDER BY CaseID;
PRINT 'IMPORTANT NOTE - PLEASE READ
If there are any Cases that require remediation Step 1, please complete Step 1 for all such CaseIDs BEFORE proceeding to Step 2.';
PRINT '';
OPEN AnalyticsCasesTables;
FETCH NEXT FROM AnalyticsCasesTables INTO @AnalyticsCaseID1;
WHILE @@FETCH_STATUS = 0
BEGIN;
    SET @ActionNeeded = 0;
    SET @AnalyticsStatusID1 = (SELECT AnalyticsStatusID FROM tblCase WHERE CaseID = @AnalyticsCaseID1)
-- Each CaseID will be evaluated for each condition
-- Condition 0: Non-Discovery Case
    IF (SELECT Type FROM tblCase WHERE CaseID = @AnalyticsCaseID1) != 101
    BEGIN;
        SELECT
        [CaseID] = @AnalyticsCaseID1
        , [Condition] = 'Non-Discovery Case'
        , [ACTION] = 'No action needed'
        , [Case Name] = (SELECT Name FROM tblCase WHERE CaseID = @AnalyticsCaseID1);
        SET @ActionNeeded = 1;
    END;
-- Condition 1: Case does not exist
    IF NOT EXISTS (SELECT CaseID FROM tblCase WHERE CaseID = @AnalyticsCaseID1) AND EXISTS (SELECT 1
    FROM sys.tables
    WHERE Name LIKE N'%[_]' + CONVERT(nvarchar(25), @AnalyticsCaseID1))
    BEGIN;
        SELECT
        [CaseID] = @AnalyticsCaseID1
        , [Condition] = 'Case does not exist'
        , [ACTION] = 'Follow Article 100051123 Step 1';
        SELECT *
        FROM sys.tables
        WHERE Name LIKE N'%[_]' + CONVERT(nvarchar(25), @AnalyticsCaseID1)
        ORDER BY name;
        SET @ActionNeeded = 1;
    END;
-- Condition 2: Analytics Disabling process not complete
    IF @AnalyticsStatusID1 IN (856, 857) AND EXISTS (SELECT 1
    FROM sys.tables
    WHERE Name LIKE N'%[_]' + CONVERT(nvarchar(25), @AnalyticsCaseID1))
    BEGIN;
        SELECT
        [CaseID] = @AnalyticsCaseID1
        , [Condition] = 'Analytics Disabling process not complete'
        , [ACTION] = 'Follow Article 100051123 Step 1 then Step 2'
        , tc.CaseID
        , tc.Name
        , tc.StatusID
        , ts.Name AS 'Case Status'
        , tc.FolderType
        , tc.LastAnalyticsStatusID
        , ts1.Name AS 'LastAnalyticsStatus'
        , tc.AnalyticsStatusID
        , ts2.Name AS 'AnalyticsStatus'
        , AnalyticsStatusText
        FROM tblCase AS tc
        JOIN tblStatus AS ts on tc.StatusID = ts.StatusID
        JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
        JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
        WHERE tc.CaseID = @AnalyticsCaseID1;
        SELECT *
        FROM sys.tables
        WHERE Name LIKE N'%[_]' + CONVERT(nvarchar(25), @AnalyticsCaseID1)
        ORDER BY name;
        SET @ActionNeeded = 1;
    END;
-- Condition 3: Analytics Disabling process partially complete
    IF @AnalyticsStatusID1 != 856 AND NOT EXISTS (SELECT 1
    FROM sys.tables
    WHERE Name LIKE N'%[_]' + CONVERT(nvarchar(25), @AnalyticsCaseID1))
    BEGIN;
        SELECT
        [CaseID] = @AnalyticsCaseID1
        , [Condition] = 'Analytics Disabling process partially complete'
        , [ACTION] = 'Follow Article 100051123 Step 2'
        , tc.Name
        , tp.PrincipalName + '(' + tp.PrincipalLogin + ')' AS 'Case Owner'
        , CASE tc.FolderType WHEN 330 THEN 'Case'
        WHEN 331 THEN 'Research Folder'
        WHEN 332 THEN 'Hidden Research Folder (Analytics incapable)'
        ELSE 'Unknown' END AS 'Case Type'
        , tc.StatusID
        , ts.Name AS 'Case Status'
        , tc.FolderType
        , tc.LastAnalyticsStatusID
        , ts1.Name AS 'LastAnalyticsStatus'
        , tc.AnalyticsStatusID
        , ts2.Name AS 'AnalyticsStatus'
        , AnalyticsStatusText
        FROM tblCase AS tc
        JOIN tblStatus AS ts on tc.StatusID = ts.StatusID
        JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
        JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
        JOIN tblPrincipal AS tp ON tc.OwnerPrincipalID = tp.PrincipalID
        WHERE tc.CaseID = @AnalyticsCaseID1;
        SET @ActionNeeded = 1;
    END;
    IF @ActionNeeded = 0
    BEGIN;
        SELECT
        [ACTION] = 'No action needed'
        , [CaseID] = @AnalyticsCaseID1;
    END;
    FETCH NEXT FROM AnalyticsCasesTables INTO @AnalyticsCaseID1;
END;
CLOSE AnalyticsCasesTables;
DEALLOCATE AnalyticsCasesTables;
IF (OBJECT_ID('tempdb..#AnalyticsCaseID1') IS NOT NULL) EXEC sp_executesql N'DROP TABLE #AnalyticsCaseID1;';
GO

After both queries have been run, review their output and apply the applicable remediation steps:

1. Condition 1:
- Query 1 shows the Case Status as Deleted, Last Analytics Status as Disabling Analytics, Current Analytics Status as Analytics Disabled, and there are no Analytics tables present.
- Query 2 lists No action needed for the CaseID.

Condition 1 remediation steps: Obtain the assistance from the onsite SQL team to delete the SQL Agent Job titled RuleEngine Job for Case _.

2. Condition 2:

- Query 1 shows the Case Status as Deleted and there are no Analytics tables present.
- Query 2 lists Follow Article 100051123 Step 2.

Condition 2 remediation steps: Please follow the Solution Steps in Related Article Discovery Accelerator upgrade fails with "Cannot find the object" below and contact Technical Support.

3. Condition 3:

- Query 1 shows the Case Status as Deleted and there are Analytics tables present.
- Query 2 lists Follow Article 100051123 Step 1 then Step 2.

Condition 3 remediation steps: Please follow the Solution Steps in Related Article Discovery Accelerator upgrade fails with "Cannot find the object" below and contact Technical Support.

4. Condition 4:

- Query 1 shows the Case does not exist in the database and there are Analytics tables present.
- Query 2 lists Follow Article 100051123 Step 1.

Condition 4 remediation steps: Please follow the Solution Steps in Related Article Discovery Accelerator upgrade fails with "Cannot find the object" below and contact Technical Support.