SCOM 2012: After upgrading to SP1, the ACS database is not updated

After we upgrade to System Center Operations Manager 2012 Service Pack 1

We see that the ACS Collector Service is crashing while attempting to create a new partition.

When manually running the upgrade script [%Windows%\System32\Security\AdtServer\DbUpgV7toV8.sql] for the ACS database we get the below error complaining about some missing views: 

Msg 2714, Level 16, State 6, Line 1 

There is already an object named 'dtClaimString_15eb844f_905f_4b7b_bf57_61e549511f95' in the database.

Solution

=============
Run this SQL Query - Updates ACS DB schema from V7 to V8

##########################################

/******************************************************************************

 *

 * DbUpgV7toV8.sql

 *

 * Updates ACS DB schema from V7 to V8

 *

 ******************************************************************************/

declare @iVersion    int -- current schema version

set @iVersion = (select Value from dtConfig where Id = 2)

if (@iVersion = 7)

begin

    begin tran

    -- update description for existing category

    if exists (select * from dtCategory where Id = 0)

        update dtCategory set Description = N'ACS' where Id = 0

    else

        insert into dtCategory  (Id, Description)                   values (0, N'ACS')

    -- insert new category entries

    if not exists (select * from dtCategory where Id = 101)

        insert into dtCategory  (Id, Description)                   values (   101, N'Event processing')

    if not exists (select * from dtCategory where Id = 103)

        insert into dtCategory  (Id, Description)                   values (   103, N'Service shutdown')

    if not exists (select * from dtCategory where Id = 0x3109)

        insert into dtCategory  (Id, Description)                   values (0x3109, N'User / Device Claims')

    if not exists (select * from dtCategory where Id = 0x320D)

        insert into dtCategory  (Id, Description)                   values (0x320D, N'Central Access Policy Staging')

    -- create empty dtClaimString_!g! dtOldResourceAttribute_!g! dtNewResourceAttribute_!g! dtUserClaim_!g! dtDeviceClaim_!g! tables

    declare @vchStmt nvarchar(max)

    declare @vchPartitionId nchar(36)

    declare cPartition cursor for

        select PartitionId from dtPartition order by PartitionCloseTime desc

    open cPartition

    fetch next from cPartition into @vchPartitionId

    while @@fetch_status = 0

    begin

        set @vchStmt =  N'create table dtClaimString_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'('

        set @vchStmt =  @vchStmt + N'Id int not null constraint pkClaimString_' + @vchPartitionId + ' primary key identity     (0,1),'

        set @vchStmt =  @vchStmt + N'Hash                     binary(20)      not null,'

        set @vchStmt =  @vchStmt + N'strClaimId               nvarchar(max)   not null,'

        set @vchStmt =  @vchStmt + N'strClaimDisplayName      nvarchar(max)   not null,'

        set @vchStmt =  @vchStmt + N'strClaimValue            nvarchar(max)   not null,'

        set @vchStmt =  @vchStmt + N'strClaimValueDisplayName nvarchar(max)   not null'

        set @vchStmt =  @vchStmt + N')'

        if not exists (select * from sys.sysobjects where name = 'dtClaimString_' + @vchPartitionId + N' ')

     exec (@vchStmt)

  

        set @vchStmt =  N'create table dtOldResourceAttribute_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'('

        set @vchStmt =  @vchStmt + N'EventId         bigint      not null,'

        set @vchStmt =  @vchStmt + N'CreationTime    datetime    not null,'

        set @vchStmt =  @vchStmt + N'ClaimId         int         not null    constraint fkOldResourceAttributeClaimId_' + @vchPartitionId + N' references dtClaimString_' + @vchPartitionId + N' (Id),'

        set @vchStmt =  @vchStmt + N')'

        if not exists (select * from sys.sysobjects where name = 'dtOldResourceAttribute_' + @vchPartitionId + N' ')

     exec (@vchStmt)

        set @vchStmt =  N'create table dtNewResourceAttribute_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'('

        set @vchStmt =  @vchStmt + N'EventId         bigint      not null,'

        set @vchStmt =  @vchStmt + N'CreationTime    datetime    not null,'

        set @vchStmt =  @vchStmt + N'ClaimId         int         not null    constraint fkNewResourceAttributeClaimId_' + @vchPartitionId + N' references dtClaimString_' + @vchPartitionId + N' (Id),'

        set @vchStmt =  @vchStmt + N')'

        if not exists (select * from sys.sysobjects where name = 'dtNewResourceAttribute_' + @vchPartitionId + N' ')

     exec (@vchStmt)

    

        set @vchStmt =  N'create table dtUserClaim_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'('

        set @vchStmt =  @vchStmt + N'EventId         bigint      not null,'

        set @vchStmt =  @vchStmt + N'CreationTime    datetime    not null,'

        set @vchStmt =  @vchStmt + N'ClaimId         int         not null    constraint fkUserClaimClaimId_' + @vchPartitionId + N' references dtClaimString_' + @vchPartitionId + N' (Id),'

        set @vchStmt =  @vchStmt + N')'

        if not exists (select * from sys.sysobjects where name = 'dtUserClaim_' + @vchPartitionId + N' ')

     exec (@vchStmt)

        set @vchStmt =  N'create table dtDeviceClaim_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'('

        set @vchStmt =  @vchStmt + N'EventId         bigint      not null,'

        set @vchStmt =  @vchStmt + N'CreationTime    datetime    not null,'

        set @vchStmt =  @vchStmt + N'ClaimId         int         not null    constraint fkDeviceClaimClaimId_' + @vchPartitionId + N' references dtClaimString_' + @vchPartitionId + N' (Id),'

        set @vchStmt =  @vchStmt + N')'

        if not exists (select * from sys.sysobjects where name = 'dtDeviceClaim_' + @vchPartitionId + N' ')

    exec (@vchStmt)

        fetch next from cPartition into @vchPartitionId

    end

    close cPartition

    deallocate cPartition

    -- create empty dvUserClaims_!g! dvDeviceClaims_!g! dvNewResourceAttribute_!g! dvOldResourceAttribute_!g! views

    declare cPartition cursor for

        select PartitionId from dtPartition order by PartitionCloseTime desc

    open cPartition

    fetch next from cPartition into @vchPartitionId

    while @@fetch_status = 0

    begin

        set @vchStmt =  N'create view dvUserClaims_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'as '

        set @vchStmt =  @vchStmt + N'select '

        set @vchStmt =  @vchStmt + N'    uc.EventId as EventId, '

        set @vchStmt =  @vchStmt + N'    uc.CreationTime as CreationTime, '

        set @vchStmt =  @vchStmt + N'    ucs.strClaimId as UserClaimId, '

        set @vchStmt =  @vchStmt + N'    ucs.strClaimDisplayName as UserClaimDisplayName, '

        set @vchStmt =  @vchStmt + N'    ucs.strClaimValue as UserClaimValue, '

        set @vchStmt =  @vchStmt + N'    ucs.strClaimValueDisplayName as UserClaimValueDisplayName '

        set @vchStmt =  @vchStmt + N'FROM '

        set @vchStmt =  @vchStmt + N'    dtUserClaim_' + @vchPartitionId + ' as uc '

        set @vchStmt =  @vchStmt + N'    inner join dtClaimString_' + @vchPartitionId + ' ucs on ucs.Id = uc.ClaimId '

 

        if not exists (select * from sys.sysobjects where name = 'dvUserClaims_' + @vchPartitionId + N' ')

    exec (@vchStmt)

        set @vchStmt =  N'create view dvDeviceClaims_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'as '

        set @vchStmt =  @vchStmt + N'select '

        set @vchStmt =  @vchStmt + N'    dc.EventId as EventId, '

        set @vchStmt =  @vchStmt + N'    dc.CreationTime as CreationTime, '

        set @vchStmt =  @vchStmt + N'    dcs.strClaimId as DeviceClaimId, '

        set @vchStmt =  @vchStmt + N'    dcs.strClaimDisplayName as DeviceClaimDisplayName, '

        set @vchStmt =  @vchStmt + N'    dcs.strClaimValue as DeviceClaimValue, '

        set @vchStmt =  @vchStmt + N'    dcs.strClaimValueDisplayName as DeviceClaimValueDisplayName '

        set @vchStmt =  @vchStmt + N'FROM '

        set @vchStmt =  @vchStmt + N'    dtDeviceClaim_' + @vchPartitionId + ' as dc '

        set @vchStmt =  @vchStmt + N'    inner join dtClaimString_' + @vchPartitionId + ' dcs on dcs.Id = dc.ClaimId '

       if not exists (select * from sys.sysobjects where name = 'dvDeviceClaims_' + @vchPartitionId + N' ')

    exec (@vchStmt)

        set @vchStmt =  N'create view dvNewResourceAttributes_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'as '

        set @vchStmt =  @vchStmt + N'select '

        set @vchStmt =  @vchStmt + N'    nra.EventId as EventId, '

        set @vchStmt =  @vchStmt + N'    nra.CreationTime as CreationTime, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimId as NewClaimId, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimDisplayName as NewClaimDisplayName, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimValue as NewClaimValue, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimValueDisplayName as NewClaimValueDisplayName '

        set @vchStmt =  @vchStmt + N'FROM '

        set @vchStmt =  @vchStmt + N'    dtNewResourceAttribute_' + @vchPartitionId + ' as nra '

        set @vchStmt =  @vchStmt + N'    inner join dtClaimString_' + @vchPartitionId + ' ncs on ncs.Id = nra.ClaimId '

       if not exists (select * from sys.sysobjects where name = 'dvNewResourceAttributes_' + @vchPartitionId + N' ')

    exec (@vchStmt)

   

        set @vchStmt =  N'create view dvOldResourceAttributes_' + @vchPartitionId + N' '

        set @vchStmt =  @vchStmt + N'as '

        set @vchStmt =  @vchStmt + N'select '

        set @vchStmt =  @vchStmt + N'    nra.EventId as EventId, '

        set @vchStmt =  @vchStmt + N'    nra.CreationTime as CreationTime, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimId as OldClaimId, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimDisplayName as OldClaimDisplayName, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimValue as OldClaimValue, '

        set @vchStmt =  @vchStmt + N'    ncs.strClaimValueDisplayName as OldClaimValueDisplayName '

        set @vchStmt =  @vchStmt + N'FROM '

        set @vchStmt =  @vchStmt + N'    dtOldResourceAttribute_' + @vchPartitionId + ' as nra '

        set @vchStmt =  @vchStmt + N'    inner join dtClaimString_' + @vchPartitionId + ' ncs on ncs.Id = nra.ClaimId '

       if not exists (select * from sys.sysobjects where name = 'dvOldResourceAttributes_' + @vchPartitionId + N' ')

    exec (@vchStmt)

        fetch next from cPartition into @vchPartitionId

    end

    close cPartition

    deallocate cPartition

    -- mark all active partitions for closing

    if (select count(*) from dtPartition where Status = 0) > 0

        update dtPartition set Status = 1 where Status = 0

    -- update schema version

    update dtConfig set Value = 8 where Id = 2

    commit tran

end

go

##########################################