After upgrading from CA Service Desk Manager (CA SDM) 12.1 (GA upgraded to 12.1 CUM#3) to 12.9, the MDB table USP_BOPSID is missing the ROLE_ID column.

Products

• CA IT Asset Manager
• CA Software Asset Manager (CA SAM)
• ASSET PORTFOLIO MGMT- SERVER
• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

Description:

After upgrading from CA Service Desk Manager (CA SDM) 12.1 (GA upgraded to 12.1 CUM#3) to 12.9, the MDB table USP_BOPSID is missing the ROLE_ID column.

Errors similar to the one below are seen in the CA SDM 12.9 STDLOGs:

SDMServer sqlagt:upinde 6992 ERROR sqlclass.c 1002 SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=207 SQL State=42S22] Invalid column name 'role_id'.

Steps to reproduce:

1. Load clean/fresh 12.1 MDB. The table USP_BOPSID does not exist.
   
   
2. Upgrade MDB to the one provided via 12.1 CUM#3. The UPS_BOPSID MDB table gets created, but not the ROLE_ID column.
   
   
3. Upgrade CA SDM application to 12.1 CUM#3
   
   
4. Upgrade MDB to CA SDM 12.9 MDB. Situation is similar to the CA SDM 12.1 CUM#3 scenario above
   
   
5. Upgrade to CA SDM application to 12.9

Following errors are seen in the CA SDM 12.9 STDLOGs

SDMServer sqlagt:upinde 6992 ERROR sqlclass.c 1002 SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=207 SQL State=42S22] Invalid column name 'role_id'.

Below are the differences between the USP_BOPSID.XML file for both CA SDM 12.1 CUM#3 and CA SDM 12.9

CA SDM 12.1 CUM#3

<Column name="bo_info" dataType="nvarchar" length="255" nullable="true" caseSensitive="false"/>
<Column name="share_session" dataType="int" nullable="true"/> 

CA SDM 12.9

<Column name="role_id" dataType="int" nullable="true"/>

Solution:

Manually create the missing ROLE_ID column in the USP_BOPSID MDB table.

The following SQL script can be used to create the missing ROLE_ID column in the USP_BOPSID MDB table

use [mdb]       
GO 
if NOT exists(select * from sys.columns
where Name = N'role_id' and Object_ID = Object_ID(N'usp_bopsid'))
begin
ALTER TABLE dbo.usp_bopsid ADD role_id int NULL
end
GO

Once the missing ROLE_ID column is created, the error messages documented above should disappear and CA SDM will function normally.

Environment

Release: UAPMAC990JPP-12.9-Asset Portfolio Management-Asset Configuration
Component: