This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice This article is not considered official documentation for K2 software and is provided “as is” with no warranties.

Issue

After upgrading to K2 Five the installation detected an error on the database and showed errors in the logs.

Symptoms

Upgrade from K2 4.7 to K2 Five fails. The error raised in the logs indicate duplicate entries cannot be inserted into the root category to resolve orphaned Forms, Views or SmartObjects.

Script execute failed: System.Exception: Script execution exception: Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UQ_Category_Data_Data_DataTypeId_SystemId'. Cannot insert duplicate key in object 'Category.Data'. The duplicate key value is ([integer], [GUID], [Integer]).

Troubleshooting Steps

Execute the script below. This will indicate any orphaned troublesome objects that are causing the errors.

If any results are returned when the script is executed, please log a support ticket with K2.

This should be done before upgrading to K2 Five. K2 support will assist in fixing this issue.

 

-- Exclude CRM SmartObjects

DECLARE @CRMMetadataSmoGuid UNIQUEIDENTIFIER = N'36F7748F-221E-417F-BF8A-F1116EF0EBF6';

DECLARE @CRMMetadataItemReferenceSmoGuid UNIQUEIDENTIFIER = N'FAA1BF09-BAEB-422C-AF64-4EFE9F3C744F';

DECLARE @CRM5Entities UNIQUEIDENTIFIER = N'D9528C8E-5047-4184-90D1-561F408F81B2';

DECLARE @CRM5Metadata UNIQUEIDENTIFIER = N'A47925DA-6AC4-43FF-8374-BA5503964BA0';

DECLARE @CRM5MetadataItemReference UNIQUEIDENTIFIER = N'3F44DBBF-F544-4676-A0A0-C3413E243FE5';

DECLARE @CRM5Workflow UNIQUEIDENTIFIER = N'50D46B6B-124A-4854-9635-85EC8CAA0C3F';

DECLARE @TargetCategoryID INT = 1;

 

DECLARE @OrphanedData TABLE

(

       [CategoryId] [int] NULL,

       [DataType] [nvarchar](255) NULL,

       [ObjectName] [nvarchar](450) NULL,

       [Data] [nvarchar](250) NULL

);

 

INSERT INTO @OrphanedData ([CategoryId], [Data], [DataType], [ObjectName])

    SELECT

              @TargetCategoryID,

              [SO].[SmartObjectGUID],

              N'SmartObject',

              [SO].[SystemName]

       FROM [SmartBroker].[SmartObject] AS [SO]

       WHERE NOT EXISTS

       (

              SELECT 1

              FROM [Category].[Data] AS [C]

              WHERE [C].[Data] = CAST([SO].[SmartObjectGUID] AS NVARCHAR(MAX))

       )

       AND [SO].[SmartObjectGUID] NOT IN

       (

              SELECT @CRMMetadataSmoGuid UNION

              SELECT @CRMMetadataItemReferenceSmoGuid UNION

              SELECT @CRM5Entities UNION

              SELECT @CRM5Metadata UNION

              SELECT @CRM5MetadataItemReference UNION

              SELECT @CRM5Workflow

       )

       AND [SO].[SystemName] NOT IN

       (

              'Audit_Report_Licensing',

        'Process_Data_Audit',

        'Activity_Data_Audit',

        'Activity_Statistics',

        'Activity_Statistics_Graph',

        'Process_Activities',

        'Process_Information',

        'Process_Statistics',

        'Process_Statistics_Graph',

        'Process_Version_Count',

        'User_Performance',

        'User_Performance_Graph',

        'Process_Instance_Audit',

        'Activity_Instance_Audit',

        'Activity_Instance_XML_Audit',

        'Process_Instance_XML_Audit',

        'ProcessPerformanceDayOnDay',

        'ProcessPerformanceDayOnDayActivity',

        'ProcessPerformanceDayOnDayDetail',

        'ProcessPerformanceMonthOnMonth',

        'ProcessPerformanceMonthOnMonthActivity',

        'ProcessPerformanceMonthOnMonthDetail',

        'ProcessPerformanceWeekOnWeek',

        'ProcessPerformanceWeekOnWeekActivity',

        'ProcessPerformanceWeekOnWeekDetail',

        'ProcessPerformanceYearOnYear',

        'ProcessPerformanceYearOnYearActivity',

        'ProcessPerformanceYearOnYearDetail',

        'UserWorkload',

        'UserWorkloadDetail',

        'UserWorkloadMeasure',

        'WorklistStatistics',

        'WorklistStatisticsItems',

        'WorkflowActivityStatus',

        'WorkflowDestinationStatus',

        'WorkflowEventStatus',

        'WorkflowPriority',

        'WorkflowProcessStatus',

        'WorkflowStatus',

        'Activity_Graph',

        'Process_Graph',

        'Interval_Graph',

        'Process_Activity_Instances',

        'Process_Sets',

        'Workflow_Participant',

        'Workflow_Activity_Destination',

              'WorkspaceImage',

              'SmartObject_Service_Functions',

              'com_K2_System_SmartForms_UrlRewriter_Routes',

              'K2_Generic_Settings_Personal_Setting',

              'K2_Generic_Settings_Shared_Setting',

              'SharePointUserFunctionsService_SharePointUsers',

              'SharePoint_Groups',

              'SharePoint_Metadata',

              'SharePointUserFunctionsService_SharePointUsers'

       );

 

SELECT

       [Data],

       COUNT(*) AS [Count]

FROM

       @OrphanedData

GROUP BY [Data]

HAVING COUNT(*) > 1;

 

WITH [View]([Data], [Count]) AS

(

       SELECT

              [Data],

              COUNT(*)

       FROM

              @OrphanedData

       GROUP BY [Data]

       HAVING COUNT(*) > 1

)

SELECT

       [OD].[Data],

       [OD].[DataType],

       [OD].[ObjectName]

FROM [View] AS [V]

INNER JOIN @OrphanedData AS [OD]

       ON [OD].[Data] = [V].[Data];