Environment
NetIQ AppManager 6.x
NetIQ AppManager 7.0.x
NetIQ AppManager 7.0.x
Situation
NetIQ PurgeData QDB and NetIQ Daily QDB fail with error messages.
The scheduled SQL Server Agent job 'NetIQ Daily QDB' fails with Error: 'DELETE statement conflicted with COLUMN REFERENCE constraint 'datalogon_DataID_fk'. The conflict occurred in database 'QDB', table 'datalogon', column 'DataID''
The scheduled SQL Server Agent job 'NetIQ PurgeData QDB' fails with Error: 'DELETE statement conflicted with COLUMN REFERENCE constraint 'datalogon_DataID_fk'. The conflict occurred in database 'QDB', table 'datalogon', column 'DataID'. [SQLSTATE 23000] (Error 547) PurgeAllData: failed to delete DataHeader where dataid = ?? [SQLSTATE 42000] (Error 50000) The statement has been terminated. [SQLSTATE 01000] (Error 3621). NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.'
The scheduled SQL Server Agent job 'NetIQ Daily QDB' fails with Error: 'DELETE statement conflicted with COLUMN REFERENCE constraint 'datalogon_DataID_fk'. The conflict occurred in database 'QDB', table 'datalogon', column 'DataID''
The scheduled SQL Server Agent job 'NetIQ PurgeData QDB' fails with Error: 'DELETE statement conflicted with COLUMN REFERENCE constraint 'datalogon_DataID_fk'. The conflict occurred in database 'QDB', table 'datalogon', column 'DataID'. [SQLSTATE 23000] (Error 547) PurgeAllData: failed to delete DataHeader where dataid = ?? [SQLSTATE 42000] (Error 50000) The statement has been terminated. [SQLSTATE 01000] (Error 3621). NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.'
Resolution
The resolution is to identify and delete the offending pointers (dataIDs) from the QDB.
To identify the offending dataIDs, use the following steps:
- Open SQL Server Query Analyzer.
- In the query window hit Ctrl+U. This will launch the change database option.
- Select the Repository (QDB) from the Select Database window and click OK.
- Press Ctrl+T to change the output to text format for the query window.
- In the query window type:
Select * from datalogon where dataid in (Select dataid from dataheader where status & 0x08000000 = 0x08000000)
- Press F5 or click the green arrow icon to execute the SQL.
To delete the offending dataIDs, use the following steps:
- Open SQL Server Query Analyzer.
- In the query window, press Ctrl+U to launch the change database option.
- Select the Repository (QDB) from the Select Database window and click OK.
- Press Ctrl+T to change the output to text format for the query window.
- In the query window type:
delete from datalogon where dataid in (Select dataid from dataheader where status & 0x08000000 = 0x08000000)
- Press F5 or click the green arrow icon to execute the SQL.
Cause
Creating a graph or chart in the Operator/Chart Console creates an entry in the datalogon table. This datalogon table entry is a pointer to the datastream required for the graph or chart. When you delete the graph or chart, this pointer should be automatically deleted. Occasionally this pointer does not get deleted. Later, when the datastream referenced by the pointer is deleted, the NetIQ PurgeData QDB and NetIQ Daily QDB tasks fail.
Additional Information
Formerly known as NETIQKB26773