kbt170408

Issue

You are unable to apply OData settings in K2 Management > Integration > APIs > SmartObject OData and receiving a 404 Not Found error while the globalization culture setting has already been set to en-US.

Symptoms

• You are unable to apply OData settings in K2 Management > Integration > APIs > SmartObject OData and receiving a 404 not found error
• The following error message is logged in the K2 host server log on every attempt to apply OData settings in K2 Management > Integration > APIs > SmartObject OData:
   

"Error","SmartObjects","10702","Error","SourceCode.SmartObjects.ServiceBroker [ExecuteSmartObject]","10702 An error occurred in the WebAPISettings Service Instance. Exception has been thrown by the target of an invocation. An item with the same key has already been added.

Resolution

This error can be caused by the presence of duplicate entries in the [SmartBroker].[SmartObject] table.

To resolve this problem you can use the following SQL scripts:

Before attempting any changes or modifications in the K2 system database make sure that you have a backup and that you have clear understanding of how to use SQL scripts. Make sure to verify results/test system behavior thoroughly immediately after applying these changes. In case of any doubts be sure to log a K2 support ticket to get assistance and clarifications.

-- #1 {SELECT DUPLICATES}
SELECT [SmartObjectGUID], [SystemName], COUNT([SmartObjectGUID]) AS 'Count', [Version] FROM [SmartBroker].[SmartObject] GROUP BY [SmartObjectGUID], [SystemName], [Version] HAVING COUNT([SmartObjectGUID]) > 1;

In newer versions of K2 Five, note the following column changes:

[SmartObjectGUID] --> [GUID]

[SystemName] --> [Name]

The [IsSytem_XML] does not exist in newer versions of K2 Five. However, you can confirm if a Smartobject is a System SmartObject by looking at the [TypesXML] property for the relevant SmartObject.

-- #2 {SAMPLE SELECT}
SELECT [SmartObjectGUID], [SystemName], [Version], [IsSytem_XML], [ID] FROM [SmartBroker].[SmartObject] WHERE [SmartObjectGUID] = 0 -- {INSERT SmartObjectGUID HERE FROM #1} AND [Version] = -1 -- {INSERT Version HERE FROM #1}

Since 5.3:

#1:
SELECT [GUID], [Name], COUNT([GUID]) AS 'Count', [Version]
FROM [SmartBroker].[SmartObject]
GROUP BY [GUID], [Name], [Version]
HAVING COUNT([GUID]) > 1;


#2:
SELECT [GUID], [GUID], [Version], [TypesXML].exist('types/type[@name=''system'']'), [ID]
FROM [SmartBroker].[SmartObject]
WHERE CAST([GUID] as nvarchar(max)) + CAST([Version] as nvarchar(max)) IN (SELECT CAST([GUID] as nvarchar(max)) + CAST([Version] as nvarchar(max))
FROM
[SmartBroker].[SmartObject]
GROUP BY
[GUID], [Name], [Version]
HAVING
COUNT([GUID]) > 1)

-- #3 {SAMPLE DELETE}
DELETE FROM [SmartBroker].[SmartObject] WHERE [ID] = 0 -- {INSERT [ID] HERE FROM #2 WHICH HAS 'IsSytem_XML' = 0}

The first script returns GUIDs of SmartObjects which have duplicate entries in the table. The second script can show all duplicate entries for specific SmartObject GUIDs returned by the first query. You can then use the third script to delete duplicate entries with lower/older ID values. Once the duplicates are removed, restart the K2 service and make sure that the error is gone from the host server log / and the issue with OData is resolved.