Data Warehouse issues after upgrading OM 2012 SP1 to R2


After upgrading SC Operations Manager 2012 SP1 to R2 you might notice general problems related to the Data Warehouse component, including Reporting of course. This may happen in situations where on upgrade there is more than one AlertDetail_GUID table in the DW database and not all will get upgraded to get the 2 new columns - TfsWorkItemId and TfsWorkItemOwner.

If you look in the Operations Manager event logs, you will notice this error event 31565 on one of the Management Servers:

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'.
One or more workflows were affected by this.
Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component 

 

To resolve the issue execute this SQL Query on the OperationsManagerDW database:

DECLARE
   @GuidString NVARCHAR(50),
   @Guid UNIQUEIDENTIFIER,
   @StandardDatasetTableMapRowId INT,
   @Statement NVARCHAR(MAX),
   @SchemaName SYSNAME,
   @TableNameSuffix SYSNAME,
   @BaseTableName SYSNAME,
   @FullTableName SYSNAME
SELECT @GuidString = DatasetId
FROM StandardDataset WITH(NOLOCK)
WHERE SchemaName = 'Alert'
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (
   SELECT *
   FROM StandardDatasetTableMap AS TM
   WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
   AND (tm.DatasetId = @GuidString)
) BEGIN
   SELECT TOP 1
      @StandardDatasetTableMapRowId = TM.StandardDatasetTableMapRowId,
      @SchemaName = SD.SchemaName,
      @TableNameSuffix = TM.TableNameSuffix,
      @BaseTableName = SDAS.BaseTableName
   FROM StandardDatasetTableMap AS TM
   JOIN StandardDataset AS SD
      ON TM.DatasetId = sd.DatasetId
   JOIN StandardDatasetAggregationStorage AS SDAS
      ON SDAS.DatasetId = TM.DatasetId AND SDAS.AggregationTypeId = TM.AggregationTypeId
   WHERE
      TM.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId AND
      TM.DatasetId = @GUIDString AND
      SDAS.TableTag = 'detail' AND
      SDAS.DependentTableInd = 1
   ORDER BY tm.StandardDatasetTableMapRowId
   SET @FullTableName = @BaseTableName + '_' + @TableNameSuffix
   IF NOT EXISTS (
      SELECT *
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE
         TABLE_NAME = @FullTableName AND
         TABLE_SCHEMA = @SchemaName AND
         COLUMN_NAME = N'TfsWorkItemId'
   ) BEGIN
      SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemId NVARCHAR(256) NULL'
      EXECUTE (@Statement)
   END
   IF NOT EXISTS (
      SELECT *
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE
         TABLE_NAME = @FullTableName AND
         TABLE_SCHEMA = @SchemaName AND
         COLUMN_NAME = N'TfsWorkItemOwner'
   ) BEGIN
      SET @Statement = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@FullTableName) + ' ADD TfsWorkItemOwner NVARCHAR(256) NULL'
      EXECUTE (@Statement)
   END
END
EXEC StandardDatasetBuildCoverView @GUIDString, 0

 

This should get you on your way 😀

Comments (0)

Skip to main content