Not able to run the usm_sp_archive_data process
Article ID: 108935
Updated On:
Products
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
Feedback
