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

Document ID : KB000022818
Last Modified Date : 10/10/2018
Show Technical Document Details
Issue:


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

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

User-added image


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
User-added image
 
 
  • In User Mapping check that 'ca_itrm' login is mapped to mdb with user ca_itrm and Default Schema ca_itrm
 
User-added image
 



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.