Archive and Purge script failing to execute

Document ID : KB000102740
Last Modified Date : 03/07/2018
Show Technical Document Details
Issue:
When we execute archive and purge stored proc with DATE/TYPE/BU paramters, it fails in below sub stored proc (which main one calls) and throws below error. we have not made any changes in catalog columns and wondering why it would throw this error. Msg 213, Level 16, State 1, Procedure usm_sp_request_data_archive, Line 45 Column name or number of supplied values does not match table definition.
Environment:
Service Catalog 14.1  
Cause:
some tables missing the newly added columns 
Resolution:
login as mdbadmin ( you will have to login as mdbadmin or sa user for it ) to your backend sql server via SQL management studio and run the following on MDB : 

alter table usm2request add locale varchar(50); 
alter table usm2request add req_for_uuid binary(16); 
alter table usm2request add req_by_uuid binary(16); 
alter table usm2request_pending_action add sequence_id int; 
alter table usm2request_item_form add is_visible int; 

ALTER TABLE dbo.usm2request DROP COLUMN req_for_uuid 
ALTER TABLE dbo.usm2request DROP COLUMN req_by_uuid 

ALTER TABLE dbo.usm2portal_template DROP COLUMN credentials 
ALTER TABLE dbo.usm2portal_template DROP COLUMN keywords 
alter table usm2portal_template add credentials nvarchar(4000) 
alter table usm2portal_template add keywords nvarchar(4000) 



After that , try again and it will fix this problem . 
Additional Information:
Archive and Purge documentation : 
https://docops.ca.com/ca-service-management/14-1/en/administering/administering-ca-service-catalog/archive-and-purge-historical-data#ArchiveandPurgeHistoricalData-ArchivetheDatainMicrosoftSQLServer