KB Update SQL Tool - Removing an existing object using CI_NO_OBJECTS

Based on object’s ID

In this example, we are going to remove a VB Sub called “Disconnect” from the Analysis schema. The image below shows the object in CAST Enlighten:

The first step is to insert the data into the CAST entry tables that will cause the object in question to be removed from the Analysis Service when the tool is run:

insert into <KB_name>.dbo.CI_NO_OBJECTS (OBJECT_ID, ERROR_ID)
select OBJECT_ID, 0
from <KB_name>.dbo.CTV_GUID_OBJECTS
where OBJECT_NAME = 'Disconnect'
go

Info

The 0 parameter will enter 0 in the ERROR_ID column of the CI_NO_OBJECTS table.
Note that you need to specify the Analysis Service database and user as above “<KB_name>.dbo” otherwise the query will fail when the job is run.

Then:

Complete the configuration of the tool and run it.
Make sure you then update the CAST System Views.

If you want to check that the object has indeed been removed successfully, you can use the following query in an SQL IDE against your Analysis Service (adapting it for your environment):

Select count(1) Cnt
From CTV_GUID_OBJECTS
Where OBJECT_NAME = 'Disconnect'

The result returns 0 for this OBJECT_NAME:

Cnt
[int]
----
0

You can also check in CAST Enlighten – F5 to refresh the Object Browser - object no longer exists:

Based on object’s GUID

Objects with an OBJECT_GUID

In this example, we are going to remove a C++ object (a Global Variable called “dberr”) from the Analysis schema. The image below shows the object in CAST Enlighten:

The first step is to retrieve the object’s OBJECT_GUID from the CAST System Views. Use the following query in an SQL IDE against your Analysis schema:

Select OBJECT_GUID
From CTV_GUID_OBJECTS
Where OBJECT_NAME = 'dberr'
Go

This query returns the OBJECT_GUID for the “dberr” Global Variable we want to remove from the Analysis schema:

C_GlVar.dberr.C_Fi."D:\TESTAPPLICATIONS\SQL SERVER\DEMO_C\EXAMPLE..C"

The next step is to insert the data into the CAST entry tables that will cause the object in question to be removed from the Analysis Service when the tool is run:

insert into <KB_name>.dbo.CI_NO_OBJECTS (OBJECT_GUID, ERROR_ID)
values ('C_GlVar.dberr.C_Fi."D:\TESTAPPLICATIONS\SQL SERVER\DEMO_C\EXAMPLE..C"', 0)
go

Info

The 0 parameter will enter 0 in the ERROR_ID column of the CI_NO_OBJECTS table.
Note that you need to specify the Analysis Service database and user as above “<KB_name>.dbo” otherwise the query will fail when the job is run.

Then:

Complete the configuration of the tool and run it?
Make sure you then update the CAST System Views.

If you want to check that the object has indeed been removed successfully, you can use the following query in an SQL IDE against your Analysis Service (adapting it for your environment):

Select count(1) Cnt
From CTV_GUID_OBJECTS
Where OBJECT_NAME = 'dberr'

The result returns 0 for this OBJECT_NAME:

Cnt
[int]
----
0

You can also check in CAST Enlighten – F5 to refresh the Object Browser - object no longer exists:

Objects without an OBJECT_GUID

Note

Note that in AIP Core ≥ 8.3.45, the table GUID_OBJECTS can no longer be used . Attempting to use it will generate and ERROR_ID = 5 on each returned row. If you have scripts using this table to generate custom OBJECT_GUIDs, for legacy technologies such as VisualBasic and PowerBuilder, you should update those scripts to use the OBJECT_ID (which is always available) instead of generating a custom OBJECT_GUID using this table. In this situation, the example below is obsolete and should not be used.

In this example, we are going to remove a T-SQL server object (a Table called “Invoice”) from the Analysis schema. The image below shows the object in CAST Enlighten:

The first step is to check that the object has no OBJECT_GUID in the CAST System Views. Use the following query in an SQL IDE against your Analysis schema:

Select OBJECT_GUID
From CTV_GUID_OBJECTS
Where OBJECT_NAME = 'Invoice'
Go

This query returns NULL for the “Invoice” Table’s OBJECT_GUID we want to remove from the Analysis schema, indicating the object does not have an OBJECT_GUID:

OBJECT_GUID
[char     ]
-----------
NULL

The next step is to insert the data into the CAST entry tables that will cause the object in question to be removed from the Analysis Service and to create the OBJECT_GUID for the object in question when the tool is run:

insert into <KB_name>.dbo.GUID_OBJECTS (OBJECT_ID, OBJECT_GUID, ERROR_ID)
select OBJECT_ID, OBJECT_NAME, 0
From <KB_name>.dbo.CTV_GUID_OBJECTS
Where OBJECT_NAME = 'Invoice'
Go
insert into <KB_name>.dbo.CI_NO_OBJECTS (OBJECT_GUID, ERROR_ID)
values ('Invoice', 0)
Go

This is in effect two queries:

The first inserts the object’s OBJECT_ID and OBJECT_NAME into the GUID_OBJECTS table to create a correspondence between the two. The object will then be given an OBJECT_GUID called “Invoices” (uses the OBJECT_NAME value).
The second inserts the data in the CI_NO_OBJECTS table to remove the object.

Info

The 0 parameter will enter 0 in the ERROR_ID column of the CI_NO_OBJECTS table.
Note that you need to specify the Analysis Service database and user as above “<KB_name>.dbo” otherwise the query will fail when the job is run.
Please see KB Update SQL Tool - Creating OBJECT_GUIDs for more information about creating OBJECT_GUIDs for objects that do not have them.

Then:

Complete the configuration of the tool and run it.
Make sure you then update the CAST System Views.

If you want to check that the object has indeed been removed successfully, you can use the following query against your Analysis Service (adapting it for your environment):

Select count(1) Cnt
From CTV_GUID_OBJECTS
Where OBJECT_NAME = 'Invoice'

The result returns 0 for this OBJECT_NAME:

Cnt
[int]
----
0

You can also check in CAST Enlighten - F5 to refresh the Object Browser - object no longer exists: