When trying to delete a computer from the DSM Explorer you get an error: DB Error: invalid column name in select query [CMM000191]

Products

• CA Client Automation

Issue/Introduction

In the DSM Explorer the following error occurs while trying to delete a computer:

DB Error: invalid column name in select query [CMM000191]

TRC_GUI*.log shows the following:

210510-02:37:13.4196222L|006920|0000241c|GUI |CDb |DbImpl_Ado.cpp |002570

|NOTIFY | Error Message: ADO Version 2.8 - ExecuteCommandPointer -COM Error:

ErrorCode:-2147217865,WordErrorCode:3127, IDispatch error #3127,

Invalid object name 'inv_usertemplate_tree'., Microsoft SQL Native Client, (null)

Similar errors could also appear in the TRC_<EngineName*>.log.

Environment

CA Client Automation - All Versions
 

Cause

This issue is caused by incorrectly configuring the ca_itrm login in SQL as a sysadmin and/or wrong mapping with user/default Schema ca_itrm in mdb database and/or if the table ownership is incorrect.
 

Resolution

Remove the sysadmin role from ca_itrm login:

• In Microsoft SQL Server Management Studio go under Security/Logins
   
• Right click on the 'ca_itrm' user and select Properties
   
• In the Login Properties window click on Server Roles and uncheck 'sysadmin'
   
• Click OK

 

• In User Mapping check that 'ca_itrm' login is mapped to mdb with user ca_itrm and Default Schema ca_itrm

 

If the 'ca_itrm' account does not have the 'sysadmin' role and/or mapping

is correct, the problem is elsewhere.
 

Check if the owner/tables mentioned in inv_table_map exist. You could execute

the SQL query below to check if there are any problems:

SELECT * FROM inv_table_map           
WHERE (tbl_owner+table_name_tree COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN       
(SELECT u.name+o.name COLLATE SQL_Latin1_General_CP1_CS_AS FROM sysobjects o, sysusers u       
WHERE o.xtype='U' AND o.uid=u.uid))       
OR       
(tbl_owner+table_name_item COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN       
(SELECT u.name+o.name COLLATE SQL_Latin1_General_CP1_CS_AS FROM sysobjects o, sysusers u       
WHERE o.xtype='U' AND o.uid=u.uid)) 

If this SQL query returns any rows, check if the tables exist and have the

same owner as the one stored in inv_table_map.

For example :
If the query returns the row for tables inv_wbeminventory_tree and inv_wbeminventory_item

with owner ca_itrm, it means that tables ca_itrm.inv_wbeminventory_tree and 

ca_itrm.inv_wbeminventory_item do not exist in mdb database.

Check in Microsoft SQL Server Management Studio if tables dbo.inv_wbeminventory_tree

and dbo.inv_wbeminventory_item exist.

If they exist, execute these commands to change the owner of these tables to ca_itrm :
 

exec sp_changeobjectowner 'dbo.inv_wbeminventory_tree', 'ca_itrm'

exec sp_changeobjectowner 'dbo.inv_wbeminventory_item', 'ca_itrm'

Normal output is :

Caution: Changing any part of an object name could break scripts and stored procedure

Additional Information

Explanations about sysadmin problem:

In the mdb database some tables are created with the schema ca_itrm. (others tables have the dbo schema).

For example additional inventory is stored in the table inv_<inventory_name>_tree and inv_<inventory_name>_item which has ca_itrm schema.

Example:

ca_itrm.inv_usertemplate_item
ca_itrm.inv_usertemplate_tree

CA ITCM connects to the SQL mdb by using the SQL user ca_itrm. This 
user has ca_itrm as the default schema.

CA ITCM executes the SQL requests without ca_itrm prefix for the tables 
belonging to the ca_itrm schema.

However, there could be a problem if the server role 'sysadmin' is assigned 
to the login ca_itrm.

When the sysadmin role is assigned to a login, the login is always mapped 
to the dbo schema.