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.

Document ID : KB000018240
Last Modified Date : 14/02/2018
Show Technical Document Details

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.