soitoolbox --archiveHistoryData <older_than_numberofdays> fails with SQL exception

Products

• CA Service Operations Insight (SOI)

Issue/Introduction

Description:

 

While executing the soitoolbox with the parameter „—archiveHistoryData“, the following error message occurs:

ERROR: Exception while processing SQL section 5 : ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "SOIArchiveDB..cichangehistory" because it does not exist or you do not have permissions. (1088) (SQLExecDirectW)')
Failed query:

/* Purge CIChangeHistory*/
IF NOT EXISTS (SELECT *
FROM SOIArchiveDB.sys.objects
WHERE object_id =
Object_id(N'SOIArchiveDB.[dbo].CIChangeHistory')
AND type IN ( N'U' ))
BEGIN
SELECT *
INTO SOIArchiveDB.. cichangehistory
FROM cichangehistory
WHERE modificationtime <= (select tmFrame from #myGlobalVars)
END
ELSE
BEGIN
SET IDENTITY_INSERT SOIArchiveDB.. cichangehistory ON
INSERT INTO [SOIArchiveDB].[dbo].cichangehistory(ChangeID,changetype, changetypeid, classname, bnodeciid, serviceciid, associationid, modificationtime, modificationtype, applicationname, username)
SELECT ChangeID,
changetype,
changetypeid,
classname,
bnodeciid,
serviceciid,
associationid,
modificationtime,
modificationtype,
applicationname,
username
FROM cichangehistory
WHERE modificationtime <= (select tmFrame from #myGlobalVars)
SET IDENTITY_INSERT SOIArchiveDB.. cichangehistory OFF
END

 

Environment

SOI 4.0 Windows Server 2008 R2

Cause

The error is occurring when trying to access the cichangehistory table. This can occur when the "(Is Identity)"property is set to 'No' for the primary key(ChangeID) of the cichangehistory table in Column Properties

changeIDSQLStudio.png

 

Please refer to the link below

https://www.blog.pythonlibrary.org/2011/01/15/sqlalchemy-programmingerror-42000-and-ms-sql/

 

Resolution

This can be resolved by setting the "Is Identity” field set to “Yes" for the primary key(ChangeID) and saving the change after which the soitoolbox --archiveHistoryData completes successfully.

Backup the SOIArchiveDB before.

It may be safer to use "ALTER" to change the properties. Please refer to the link below.

http://stackoverflow.com/questions/11802429/prevent-saving-changes-that-require-the-table-to-be-re-created-negative-effect.