error in event log Unexpected exception occurred while processing activities. - ContactActionsLogWo

Endika Carrasco asked on October 16, 2020 11:39

I have been uploading different things with staging from pre-production to production, and now in the production event log I get this error. Does anyone know why? thank you very much

Unexpected exception occurred while processing activities. Message:

Proc_OM_ActivityRecalculationQueue_FetchActivityIDs

Caused exception: Violation of PRIMARY KEY constraint 'PK__#AE4E207__07D9BBC3E7AF79CC'. Cannot insert duplicate key in object 'dbo.@DeletedIDs'. The duplicate key value is (0). The statement has been terminated.

Exception type: System.Exception Stack trace: at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex) at CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) at CMS.DataEngine.AbstractDataConnection.CMS.DataEngine.IDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction) at CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query) at CMS.DataEngine.GeneralConnection.RunQueryWithRetry(QueryParameters query, Int32 retryCount) at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query) at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords) at CMS.DataEngine.DataQueryBase1.GetDataFromDBInternal() at CMS.DataEngine.DataQueryBase1.GetDataFromDB() at CMS.DataEngine.DataQueryBase1.GetData() at CMS.DataEngine.DataQueryBase1.get_Result() at CMS.DataEngine.ConnectionHelper.ExecuteQuery(String queryName, QueryDataParameters parameters, String where, String orderBy, Int32 topN, String columns, Int32 offset, Int32 maxRecords, Int32& totalRecords) at CMS.OnlineMarketing.SqlActivityQueueRecalculationProvider.Dequeue() at CMS.ContactManagement.ContactActionsQueueProcessor.ProcessAllContactActions() at CMS.ContactManagement.ContactActionsLogWorker.ProcessActions()

Message: Violation of PRIMARY KEY constraint 'PK__#AE4E207__07D9BBC3E7AF79CC'. Cannot insert duplicate key in object 'dbo.@DeletedIDs'. The duplicate key value is (0). The statement has been terminated.

Exception type: System.Data.SqlClient.SqlException Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)

Correct Answer

Dmitry Bastron answered on October 16, 2020 18:39

Hi Endika,

Installing the latest hotfix should solve this problem. I don't know what exact hotfix number will solve this, but here in 11.0.49 it is contained for sure. You can ask support for a specific hotfix number if you can't install the latest one for some reasons. Here is what I've found in Hotfix_separated.sql for 11.0.49 hotfix:

-- HF11-98 - Change table type in Proc_OM_ActivityRecalculationQueue_FetchActivityIDs
IF TYPE_ID('Type_OM_OrderedIntegerTable_DuplicatesAllowed') IS NULL
BEGIN
    CREATE TYPE [Type_OM_OrderedIntegerTable_DuplicatesAllowed] AS TABLE
    (
        [Value] [int] NOT NULL,
        PRIMARY KEY CLUSTERED ([Value] ASC) WITH (IGNORE_DUP_KEY = ON)
    )
END
GO

ALTER PROCEDURE [Proc_OM_ActivityRecalculationQueue_FetchActivityIDs]
AS
BEGIN 
    SET NOCOUNT ON;

    DECLARE @DeletedIDs Type_OM_OrderedIntegerTable_DuplicatesAllowed

    DELETE TOP (10000) FROM [OM_ActivityRecalculationQueue] 
    OUTPUT deleted.ActivityRecalculationQueueActivityID 
    INTO @DeletedIDs

    SELECT * FROM OM_Activity INNER JOIN @DeletedIDs ON [OM_Activity].[ActivityID] = [@DeletedIDs].[Value]
END
GO
1 votesVote for this answer Unmark Correct answer

Recent Answers

Dmitry Bastron answered on October 16, 2020 18:14

Hi Endika,

What version of CMS (and hotfix) do you use? Could you also please post here the text of Proc_OM_ActivityRecalculationQueue_FetchActivityIDs stored procedure from the database?

0 votesVote for this answer Mark as a Correct answer

Endika Carrasco answered on October 16, 2020 18:23 (last edited on October 16, 2020 18:23)

Hi Dimitry

Kentico 11 and hotfix 1

the stored procedure code Proc_OM_ActivityRecalculationQueue_FetchActivityIDs:

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Proc_OM_ActivityRecalculationQueue_FetchActivityIDs] AS BEGIN SET NOCOUNT ON;

DECLARE @DeletedIDs Type_CMS_OrderedIntegerTable

DELETE TOP (10000) FROM [OM_ActivityRecalculationQueue] 
OUTPUT deleted.ActivityRecalculationQueueActivityID 
INTO @DeletedIDs

SELECT * FROM OM_Activity INNER JOIN @DeletedIDs ON [OM_Activity].[ActivityID] = [@DeletedIDs].[Value]

END

bets regards

0 votesVote for this answer Mark as a Correct answer

Endika Carrasco answered on October 20, 2020 08:58

thank you very much Dimitry

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.