Not able to run the usm_sp_archive_data process

Products

• CA Service Catalog

Issue/Introduction

Not able to run the usm_sp_archive_data getting the following error:

INFO:started archiving request object

ERROR:Error while archivingColumn name or number of supplied values does not match table definition.

Msg 3903, Level 16, State 1, Procedure usm_sp_archive_data, Line 42 [Batch Start Line 2]

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Environment

Service Catalog 17.3 All RU versions

Service Catalog 17.4 All RU versions

Cause

Tables utilized in the sp_archive_data process do not match

Resolution

1. Review all the colums in all the concerned tables that this are used by this stored procedure.

For example, for an unknown reason, it is possible that the following columns are missing in the usm2request table:

• req_for_uuid
• req_by_uuid

2. Recreate any missing columns.

In the example, create the columns by running the following commands after connecting directly to the mdb:

• alter table usm2request add req_for_uuid binary(16);
• alter table usm2request add req_by_uuid binary(16);

 

3. Run the stored procedure and confirm that the stored procedure is successful.

Additional Information

For version 17.2 GA, using "Script Table as" > "CREATE To" > "New Query Editor Window" against "dbo.usm2request" table of the mdb database results are as follows:

USE [mdb]
GO
/****** Object: Table [dbo].[usm2request] Script Date: 6/24/2019 5:13:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[usm2request](
 [request_id] [int] NOT NULL,
 [name] [nvarchar](128) NOT NULL,
 [status] [int] NOT NULL,
 [created_date] [datetime] NOT NULL,
 [modified_date] [datetime] NOT NULL,
 [completion_date] [datetime] NULL,
 [desired_date] [datetime] NULL,
 [comments] [ntext] NULL,
 [priority] [int] NULL,
 [req_for_account_id] [varchar](50) NULL,
 [req_by_account_id] [varchar](50) NULL,
 [req_for_user_id] [nvarchar](100) NULL,
 [req_by_user_id] [nvarchar](100) NULL,
 [code] [nvarchar](64) NULL,
 [domain] [varchar](50) NULL,
 [location_uuid] [binary](16) NULL,
 [context_type] [int] NOT NULL,
 [source] [tinyint] NULL,
 [locale] [varchar](50) NULL,
 [req_for_uuid] [binary](16) NULL,
 [req_by_uuid] [binary](16) NULL,
 CONSTRAINT [XPKusm2request] PRIMARY KEY CLUSTERED
(
 [request_id] 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