The KB Update SQL Tool has a built in error handling system: when a job is launched, a series of data consistency checks are carried out to check that the proposed query will execute correctly. If any problems are detected (for example attempting to insert duplicate OBJECT_GUIDs or attempting to modify a numeric property using the wrong entry table) the job execution will stop. An error ID is then inserted into the ERROR_ID column of the entry table you are updating. You can then query the CI_ERRORS entry table to extract the error information message that will help you debug the problem.

Note that problems with the SQL query itself (incorrect syntax etc.) will be displayed in the log file that is available on when the tool has been run.

Error example

In this example we show how a data consistency error is detected when the KB Update SQL Tool is executed and how to extract error information from the CI_ERRORS table.

The following query will attempt to create a parent type link (Belongs To) between the object "CASTPubs" and the T-SQL server object "byroyalty". Following a standard server object synchronization, the object "byroyalty" will be a child of the object "CASTPubs" – the name of the database it belongs to. We are attempting to make the object "byroyalty" the parent of the object "CASTPubs", which will not be accepted by the tool:

insert into <analysis_service>.dbo.CI_PARENTS (OBJECT_GUID, PARENT_GUID, ERROR_ID)
values ('CASTPubs', 'CASTPubs.PROC.byroyalty', 0)
go

When the tool is run, the process will stop on an error:

Checking data Errors while Checking Knowledge Base Modifications data

This means that the tool cannot run because it has detected an error in what the query is attempting to do. You can then use the following query in an SQL IDE against the Analysis schema to extract the error information to debug the problem:

select OBJECT_GUID, PARENT_GUID, MESSAGE
from CI_PARENTS ci
join CI_ERRORS err
on ci.ERROR_ID = err.ERROR_ID
where ci.ERROR_ID > 0

The result of this query displays the two objects involved and the error message "A cycle was detected":

OBJECT_GUID  PARENT_GUID              MESSAGE
[char        [char]                   [char]
CASTPubs     CASTPubs.PROC.byroyalty  A cycle was detected

This query in effect looks at the column ERROR_ID in the entry table in which you are inserting the information. A match (join) is then made with the ERROR_ID column in the CI_ERRORS entry table. The column MESSAGE in CI_ERRORS is then displayed.

ERROR_IDs and corresponding messages

ERROR_ID	TYPE	CI Table	Error message
1-1000	GUID	GUID_OBJECTS	1 Duplicate key row was found on OBJECT_ID, OBJECT_GUID
			2 GUID exist in Analysis Service with another OBJECT_ID
			3 OBJECT_ID exist in Analysis Service with another GUID
			4 OBJECT_ID exist in GUID_OBJECTS with another GUID
1001-2000	OBJECTS	CI_OBJECTS	1 Duplicate key row was found on OBJECT_GUID, OBJECT_TYPE
			2 Duplicate key was found on OBJECT_GUID
			3 The same GUID was found for many objects with the same types
			4 The same GUID was found for many objects with different types
			5 Invalid object type
			6 The parent of the object is missing
2001-3000	PROPERTIES
2001-2400	INTEGER	CI_INT_PROPERTIES	1 Duplicate key row was found on OBJECT_GUID, PROP_NAME
			2 Inappropriate property types
			3 Invalid property type
			4 OBJECT_ID or OBEJCT_GUID must be provided
2401-2800	STRING	CI_STR_PROPERTIES	1 Duplicate key row was found
			2 Inappropriate property types
			3 Invalid property type
			4 OBJECT_ID or OBEJCT_GUID must be provided
2801-3000	SUPPRESSION	CI_NO_PROPERTIES	1 Duplicate key row was found
			2 Object cannot be found in Analysis Service
			3 Invalid property type
			4 OBJECT_ID or OBEJCT_GUID must be provided
3001-4000	PARENTS	CI_PARENTS	1 Duplicate key row was found
			2 Object cannot be found
			3 Parent cannot be found
			4 A cycle was detected
			5 PARENT_ID or PARENT_GUID must be provided
4001-5000	LINKS
4001-4500	ADD	CI_LINKS	1 Duplicate key row was found
			2 Caller cannot be found in Analysis Service
			3 Called cannot be found in Analysis Service
			4 Caller and Called are missing
			5 Invalid link type
			6 (CALLER_ID and CALLER_GUID) or (CALLED_ID and CALLED_GUID) are missing
4501-5000	REMOVE	CI_NO_LINKS	1 Duplicate key row was found
			2 Caller cannot be found in Analysis Service
			3 Called cannot be found in Analysis Service
			4 Caller and Called are missing
			5 (CALLER_ID and CALLER_GUID) or (CALLED_ID and CALLED_GUID) are missing
5001-6000	OBJECTS SET	CI_OBJECTS_SET	1 Duplicate key row was found
			2 Objects cannot be found in Analysis Service
			3 Objects not allowed in Sets
			4 OBJECT_ID or OBJECT_GUID must be provided

KB Update SQL Tool - Error handling

Error example

ERROR_IDs and corresponding messages