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

book

Article ID: 36889

calendar_today

Updated On:

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: