Not able to run the usm_sp_archive_data

book

Article ID: 108935

calendar_today

Updated On:

Products

CA Service Catalog CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

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

Clarity Service Management 17.2
Clarity Service Management 17.1

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

Attachments