OpsMgr 2007: Reports are blank - Event ID: 31565 generated on the RMS
We're starting to see more of these issues come in so I wanted to make sure you all were aware of it. Thanks to Support Engineer Milan Jajal for uncovering this:
========
Issue: After the installation of System Center Operations Manager 2007 Reporting, no reports are listed under Reporting pane in the Operations Console.
In the Operations Manager Event log of the RMS server the event below can be found:
Event Type: Error
Event Source: Health Service Modules
Event Category: Data Warehouse
Event ID: 31565
Description: Failed to deploy Data Warehouse component. The operation will be retried.
Exception 'SqlScriptException': Batch ordinal: 45; Exception: Invalid column name 'TopLevelHostManagedEntityRowId'.
One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component
Instance name: H2-SCOMRMS01.<domain>
Instance ID: {051D9268-58DA-7CB9-CBEA-D05B4A58EA11}
Management group: <group>
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Cause: The above event can occur if the Column 'TopLevelHostManagedEntityRowId' is missing in the ManagedEntity table in the OperationsManagerDW database. This 'TopLevelHostManagedEntityRowId' column is available in the Service Pack 1 version of OperationsManagerDW database and if the database version is lower than SP1 then the column will not be there.
If the OperationsManager database is at SP1 and OperationsManagerDW database is still at the RTM version then the Data transfer will fail and generate the error above.
It has also been found in some cases that if the OperationsManagerDW database is created using the DBCreateWizard of OpsMgr 2007 RTM Media and not yet upgraded to OpsMgr 2007 SP1, the above column will be missing.
Resolution:
1. Upgrade the OperationsManagerDW (OpsMgr 2007 Reporting) database to SP1 using the OpsMgr 2007 Upgrade wizard and select to upgrade the OpsMgr 2007 Reporting component.
2. Restart all the SQL services on the database server and all three services for OpsMgr 2007 on the RMS server.
After a few minutes all the reports will begin showing up in Reporting pane of the Operations Console.
Hopefully you won't run into this but if you do now you'll know the cause and the fix and maybe it'll save you some time and aggravation.
J.C. Hornbeck | Manageability Knowledge Engineer
