K2 Upgrade from 4.7 to Five fails with Installation Health issue
kbt143738
PRODUCTIssue
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.
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];
