VERIFIED SOLUTION i

Resolve "The imported Active Content conflicts with the current one" when importing a newer version of an Active Content in EngageOne Server

Products affected : EngageOne Server version 3.0, 3.1.1.1, and 3.1.2 Build 13 and older.

Issue

Importing a newer version of an Active Content (to overwrite the existing one) would result in an error of "The imported Active Content conflicts with the current one" e.g.

User-added image

Cause

The error is due to a Bug present in older versions of EngageOne Server (version 3.1.2.13 and older, including version 3.0 and 3.1.1.1).

The Bug potentially allows the Active Content import activities to damage some database records pertinent to the Active Content in question, which then leads to an error when the newer version of same Active Content is imported.

Resolution

UPDATED: November 15, 2017
Please follow through the steps below in order to fix the current problem. Please be advised that the fix will not stop the same issue from potentially reoccurring in the future. The only way to prevent the same problem from reoccurring is via the upgrade to at least EngageOne Server version 3.1.2.14.

Please raise a Support Case or email software.support@pb.com if you have any doubts regarding the described steps below.

Preliminary steps:  
Create full SQL Server Database Backup. 
 
Installation steps: 
1.  Start SQL Server Management Studio. 
2.  Connect to the database you want to correct. It should be the same database which has been previously backed up. 
3.  Execute the following database query on a database you want to correct. One new stored procedure should be created and compiled upon successful execution: usp_i_ac_resources_correction. 
 
CREATE procedure [dbo].[usp_i_ac_resources_correction]
as

BEGIN

    SET NOCOUNT ON

    DECLARE  
    @LV_DOC_RESOURCE_ID    bigint, 
    @LV_RESOURCE_ID        bigint,     
    @LV_AC_NAME         nvarchar(100)
    
    CREATE TABLE #IDENTIFIERS (
                CURRENT_ID    bigint,
                CORRECT_ID  bigint)    
    
  DECLARE CR_AC CURSOR FOR SELECT Doc_Resource_ID, Active_Content_Name FROM Ocm_Active_Content
  
  DECLARE CR_IDENTIFIERS CURSOR FOR SELECT CURRENT_ID, CORRECT_ID FROM #IDENTIFIERS WHERE CURRENT_ID <> CORRECT_ID

  OPEN CR_AC

  FETCH FROM CR_AC INTO @LV_DOC_RESOURCE_ID, @LV_AC_NAME

  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    SELECT @LV_RESOURCE_ID = DOC_RESOURCE_ID FROM DOC_RESOURCES WHERE FR_NAME = @LV_AC_NAME AND Status = 'C'

    INSERT INTO #IDENTIFIERS (CURRENT_ID, CORRECT_ID) VALUES (@LV_DOC_RESOURCE_ID, @LV_RESOURCE_ID)
    
    FETCH FROM CR_AC INTO @LV_DOC_RESOURCE_ID, @LV_AC_NAME
  END
  
  CLOSE CR_AC  
  DEALLOCATE CR_AC
  
  OPEN CR_IDENTIFIERS

  FETCH FROM CR_IDENTIFIERS INTO @LV_DOC_RESOURCE_ID, @LV_RESOURCE_ID

  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    UPDATE Ocm_Active_Content SET DOC_RESOURCE_ID = @LV_RESOURCE_ID WHERE DOC_RESOURCE_ID = @LV_DOC_RESOURCE_ID
    
    FETCH FROM CR_IDENTIFIERS INTO @LV_DOC_RESOURCE_ID, @LV_RESOURCE_ID
  END
  
  CLOSE CR_IDENTIFIERS  
  DEALLOCATE CR_IDENTIFIERS
  
  SET NOCOUNT OFF
  
  DROP TABLE #IDENTIFIERS
END
GO



Correction steps: 
In order to fix broken Active Content entries the following steps need to be performed: 
1.  Stored procedure usp_i_ac_resources_correction should be executed. It does not require any arguments. The proper command looks like below: 
exec usp_i_ac_resources_correction; 
 
2.  After successful execution of the mentioned procedure, all entries in OCM_Active_Content table will be pointing to most recently imported Active Content entry from Doc_Resources table. 
3.  As a sanity check we can run 2 queries, and both should give same result: 
 
select Count(*) from Doc_Resources where Doc_Resource_ID in (Select 
Doc_Resource_ID from Ocm_Active_Content) and Status ='C'; 

 
 
select Count(*) from Ocm_Active_Content; 
 
 
Recovery actions needed to revert to the previous state:  
Restore database from backup.