usm_sp_archive_data does NOT archive any data

Products

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

Issue/Introduction

when we performed archiving catalog requests , we didn't see the requests being archived .  As we checked usm_request table quickly , we didn't see the row number  decreased after  usm-sp-archive-data  stored procedure was executed succesfully 

Cause

When you perform the catalog usm_sp_achive_data stored sql procedure in SQL management  studio , you run the sql script like the following :
----------------------------------------------
USE [mdb] 
GO 
DECLARE @return_value int 
EXEC @return_value = [dbo].[usm_sp_archive_data] 
@p_object_type = N'Request', 
@p_date = N'2017-12-30', 
@p_bu = N'ca' 

SELECT 'Return Value' = @return_value 
GO
---------------------------------------------------


In above ,   you need to provide  the value  for  @p_object_type ,   @p_date  , @p_bu   .    The value for   for  @p_object_type and @p_bu     have to be case-sensitive  based on some SQL server's case-sensitive setting.   

For example ,  if you specify  @p_object_type = N'Request'   as  @p_object_type = N'request'   .  it will not archive any data sometimes .  

Environment

Service Catalog 14.1 , 17.1 

Resolution

@p_object_type  needs to either Request or Audit     

@p_bu  needs to be exactly the same as your catalog's business unit value  , case-sensitive 

Additional Information

https://docops.ca.com/ca-service-management/14-1/en/administering/administering-ca-service-catalog/archive-and-purge-historical-data#ArchiveandPurgeHistoricalData-ArchivetheDatainMicrosoftSQLServer