Hi everyone, Brian McDermott here with another Operations Manager support tip for you. If you have upgraded to System Center 2012 R2 Operations Manager (OpsMgr 2012 R2) from System Center 2012 Operations Manager Service Pack 1 (OpsMgr 2012 SP1) you may receive the following error:
Log Name: Operations Manager
Source: Health Service Modules
Event ID: 31565
Task Category: Data Warehouse
Failed to deploy Data Warehouse component. The operation will be retried.
Exception 'DeploymentException': Failed to perform Data Warehouse component deployment operation: Install; Component: DataSet, Id: '0d698dff-9b7e-24d1-8a74-4657b86a59f8', Management Pack Version-dependent Id: '29a3dd22-8645-bae5-e255-9b56bf0b12a8'; Target: DataSet, Id: '23ee52b1-51fb-469b-ab18-e6b4be37ab35'. Batch ordinal: 3; Exception: Sql execution failed. Error 207, Level 16, State 1, Procedure vAlertDetail, Line 18,
Message: Invalid column name 'TfsWorkItemId'.
The reason this is happening is that we have updated the Alert tables to add a couple of columns to better enable TFS integration, and unfortunately in some situations the upgrade code that adds these columns to the Data Warehouse tables is failing to complete successfully.
So if you are seeing the errors above when logged on one of your management servers, which, VERY SPECIFICALLY has the following in the description:
Message: Invalid column name 'TfsWorkItemId'
, then you should run the SQL statement below to add those columns to the tables that are missing them.
First, since we are directly editing the database it is absolutely essential that we begin by taking backups of both of your Operations Manager databases. Yes, both the OperationsManager DB and the OperationsManagerDW DB. Recovery of the databases can sometime require both of them so prior to any important change always backup both of them.
Also please note that the Event ID 31565 noted above is a very generic error and you should only run the SQL below if the description identifies that it is the problem with the
column. If there is a different description for the 31565 event and you need further assistance in troubleshooting it, or also if you have encountered this problem but are uncertain on how to follow the recovery steps, then please open a case with Microsoft Support and we will be glad to assist.
The Recovery Steps
On your SQL server hosting the Operations Manager Data Warehouse DB, open up SQL Server Management Studio. Connect to the Data Warehouse DB, then paste in the following code and run it to correct the problem.
The code is listed here:
DECLARE @GuidString NVARCHAR(50)
SELECT @GuidString = DatasetId FROM StandardDataset
WHERE SchemaName = 'Alert'
-- update all tables that were already created
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap tm
WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)