List of potential SQL errors seen in the 2D Map after improper SQL 2008 upgrade to SQL 2012 and how to overcome/resolve

Products

• CA Unicenter NSM

Issue/Introduction

Issue:

The below list of potential errors are found in the 2D Map after the NSM MDB has been upgraded from SQL 2008 to SQL 2012 as a result of the MDB Migration procedure not being followed correctly:

1. 2D Map crashing
2. a lot of objects in Unplaced Objects folder
3. Unable to delete many objects in the Unplaced Objects folder
4. **ERROR 53** Repository error when trying to Open Details, ...
5. "CAE0326: Required parameter IPV6/IPV4 address missing." Error appears when trying to delete the object in the Unplaced Objects folder
6. New servers discovered are placed in the Unplaced Objects folder

Warning: The procedure resolves the above listed errors but the changes may not be dynamic and closing/opening of 2D Map is always required for the changes to be observed. The procedure contains modification of the SQL query on the SQL 2012 machine - before proceeding further, it is recommended to take backup of the existing MDB and restore the original MDB back if the issue is not resolved

Environment:

• SQL 2012 running on Windows 2008/2012

 

Cause:

• MDB Migration procedure not correctly followed upgrading from SQL 2008 to SQL 2012

 

Workaround:

Open the SQL Management Studio on the SQL 2012 MDB machine with NSM installed and navigate as below:

1. under Databases select mdb
2. select Tables
3. search for the table dbo.tng_managedobject
4. under dbo.tng_managedobject
5. select Triggers and now double click on tng_tu_managedobject
6. From the SQL query which opens on the right side, search for:

if(update(severity)orupdate(status_no))

if(( 1 in(select acknowledge from deleted))and( 1 in

select acknowledge from inserted)))

begin

select 80001

raiserror 80001 'Update severity or status_no failed Since one of acknowledges is on(1). '

rollbacktransaction

return

end

 

• Observe there are no opening and closing parenthesis for the below line (Do not make changes if you observe opening and closing parenthesis in your environment):

raiserror 80001 'Update severity or status_no failed Since one of acknowledges is on(1). '

• Replace the above entire line with: 

raiserror ('Update severity or status_no failed Since one of acknowledges is on(1). ',80001,1)

 

• Also search for:

if(update(class_name)orupdate(uuid))

begin

select 80002

raiserror 80002 'Do not allow to update uuid of an object,

the transaction failed. '

rollbacktransaction

return

end

 

• Again, observe there are no opening and closing parenthesis for the below line (Do not make changes if you observe opening and closing parenthesis in your environment):

raiserror 80002 'Do not allow to update uuid of an object,

the transaction failed. '

• Replace the above entire line with:

raiserror ('Do not allow to update uuid of an object, the transaction failed. ',80002 ,2)

 

• Our Final query should look similar to below:

 

.

.

.

if(update(severity)orupdate(status_no))

if(( 1 in(select acknowledge from deleted))and( 1 in

select acknowledge from inserted)))

begin

select 80001

raiserror ('Update severity or status_no failed Since one of acknowledges is on(1). ',80001,1)

rollbacktransaction

return

end

(update(class_name)orupdate(uuid))

begin

select 80002

raiserror ('Do not allow to update uuid of an object, the transaction failed. ',80002 ,2)

rollbacktransaction

return

end

.

.

.

 

• If you see the below two Message then we are all set:
• Now click on anywhere on the white space in the query screen (this is just to ensure no word is selected/highlighted) and click on the Red Excalamatory execute icon ! 
  • Command(s) completed successful
  • the message with green tick mark Query executed successfully
• Now save the query as below: 
  • Click on File
  • Save SQLQuery.sql (usually under C:\Users\Administrator\Documents\SQL Server Management Studio)
• Now, close the 2D Map, restart the unicenter and awservices using the commands (or reboot the SQL 2012 machine):
  • unicntrl stop all
  • unicntrl start all
  • awservices stop
  • awservices start

 

Note: Please revert back the changes if the issue is not resolved  

Environment

Release: TNGEXO05500-11.2-Management-for Microsoft Exchange
Component: