Service Catalog Request Management configurations updates fail

Products

• CA Service Catalog

Issue/Introduction

Service Catalog MDB Database has been migrated from an old environment to a new one, and now Request Management Configuration changes are failing.

Environment

Release : 17.2

Component : CA SERVICE CATALOG

Resolution

The reason Configuration changes would not save is because the ID column of usm_configuration is missing its Identity status after the database copy.

This is the SQL syntax for creating a new copy of the table, for reference:

USE [mdb]
GO

/****** Object:  Table [dbo].[usm_configuration]    Script Date: 4/23/2021 11:28:53 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[usm_configuration](
 [group_name] [nvarchar](50) NOT NULL,
 [name] [nvarchar](50) NOT NULL,
 [domain] [varchar](50) NOT NULL,
 [data_type] [int] NOT NULL,
 [is_visible] [int] NOT NULL,
 [value] [nvarchar](2000) NULL,
 [component_id] [int] NULL,
 [aclinfo] [varchar](128) NULL,
 [product_code] [nvarchar](64) NULL,
 [widget_type] [int] NULL,
 [value_list] [ntext] NULL,
 [id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [XPKusm_configuration] PRIMARY KEY CLUSTERED 
(
 [group_name] ASC,
 [name] ASC,
 [domain] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Because the creation of the MDB while copying across from the old server is missing this property, the DB server expects a value to be supplied for the id field, and fails the insert when it is not supplied.

Investigating further, what we would need to do 

1) stop Service Catalog so the MDB isn't in use

2) take a backup, just in case

3) add a new column to the usm_configuration table as an identity

4) drop the false id column

5) rename that new one to id

6) restart everything and verify it all works now.

Those SQL commands for the create, drop and rename are

alter table usm_configuration add id int IDENTITY(1,1) NOT NULL;

alter table usm_configuration drop column id;

exec sp_rename 'usm_configuration.id2', 'id', 'COLUMN';

In addition, finding which configurations had been made in Request Management Configuration and need to be brought across to the new system can be performed with this SQL query:

select * from usm_configuration where domain <> 'INSTALLED_DEFAULT'