PRINT N'Dropping [mig_tracking].[DF_EntityEventCount_UserState]...'; GO ALTER TABLE [mig_tracking].[EntityEventCount] DROP CONSTRAINT [DF_EntityEventCount_UserState]; GO PRINT N'Dropping [mig_tracking].[DF_PartitionEventCount_UserState]...'; GO ALTER TABLE [mig_tracking].[PartitionEventCount] DROP CONSTRAINT [DF_PartitionEventCount_UserState]; GO PRINT N'Altering [mig_tracking].[BaselinePartitionEventCount]...'; GO BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; UPDATE [mig_tracking].[BaselinePartitionEventCount] SET [EventCount] = 0 WHERE [EventCount] IS NULL; ALTER TABLE [mig_tracking].[BaselinePartitionEventCount] ALTER COLUMN [EventCount] BIGINT NOT NULL; COMMIT TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; ALTER TABLE [mig_tracking].[BaselinePartitionEventCount] ADD [ItemCount] BIGINT NOT NULL, CONSTRAINT [SD_BaselinePartitionEventCount_f57199d25e574ce3b59444dbe380ea05] DEFAULT 0 FOR [ItemCount]; ALTER TABLE [mig_tracking].[BaselinePartitionEventCount] DROP CONSTRAINT [SD_BaselinePartitionEventCount_f57199d25e574ce3b59444dbe380ea05]; COMMIT TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO PRINT N'Starting rebuilding table [mig_tracking].[EntityEventCount]...'; GO BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [mig_tracking].[tmp_ms_xx_EntityEventCount] ( [MessageID] UNIQUEIDENTIFIER NOT NULL, [EntityID] UNIQUEIDENTIFIER NOT NULL, [AutomaticStateCode] INT NOT NULL, [UserStateCode] INT CONSTRAINT [DF_EntityEventCount_UserState] DEFAULT ((0)) NOT NULL, [ActualCount] BIGINT NOT NULL, [BaselineCount] BIGINT NULL, [ActualItemCount] BIGINT NOT NULL, [BaselineItemCount] BIGINT NULL, [RunDate] DATETIME NOT NULL, CONSTRAINT [tmp_ms_xx_constraint_PK_EntityEventCount1] PRIMARY KEY CLUSTERED ([MessageID] ASC, [EntityID] ASC) ); ALTER TABLE [mig_tracking].[tmp_ms_xx_EntityEventCount] ADD CONSTRAINT [SD_EntityEventCount_9219564377064d6cba295fccecd1e141] DEFAULT 0 FOR [ActualItemCount]; UPDATE [mig_tracking].[EntityEventCount] SET [ActualCount] = 0 WHERE [ActualCount] IS NULL; IF EXISTS (SELECT TOP 1 1 FROM [mig_tracking].[EntityEventCount]) BEGIN INSERT INTO [mig_tracking].[tmp_ms_xx_EntityEventCount] ([MessageID], [EntityID], [AutomaticStateCode], [UserStateCode], [ActualCount], [BaselineCount], [RunDate]) SELECT [MessageID], [EntityID], [AutomaticStateCode], [UserStateCode], [ActualCount], [BaselineCount], [RunDate] FROM [mig_tracking].[EntityEventCount] ORDER BY [MessageID] ASC, [EntityID] ASC; END ALTER TABLE [mig_tracking].[tmp_ms_xx_EntityEventCount] DROP CONSTRAINT [SD_EntityEventCount_9219564377064d6cba295fccecd1e141]; DROP TABLE [mig_tracking].[EntityEventCount]; EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_EntityEventCount]', N'EntityEventCount'; EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_constraint_PK_EntityEventCount1]', N'PK_EntityEventCount', N'OBJECT'; COMMIT TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO PRINT N'Starting rebuilding table [mig_tracking].[PartitionEventCount]...'; GO BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [mig_tracking].[tmp_ms_xx_PartitionEventCount] ( [MessageID] UNIQUEIDENTIFIER NOT NULL, [EntityID] UNIQUEIDENTIFIER NOT NULL, [PartitionValue] VARCHAR (50) NOT NULL, [AutomaticStateCode] INT NULL, [UserStateCode] INT CONSTRAINT [DF_PartitionEventCount_UserState] DEFAULT ((0)) NOT NULL, [ActualCount] BIGINT NOT NULL, [BaselineCount] BIGINT NULL, [ActualItemCount] BIGINT NOT NULL, [BaselineItemCount] BIGINT NULL, [RunDate] DATETIME NOT NULL, CONSTRAINT [tmp_ms_xx_constraint_PK_PartitionEventCount1] PRIMARY KEY CLUSTERED ([MessageID] ASC, [EntityID] ASC, [PartitionValue] ASC) ); ALTER TABLE [mig_tracking].[tmp_ms_xx_PartitionEventCount] ADD CONSTRAINT [SD_PartitionEventCount_abdb54bbcee24b9d8c49be301ad956a4] DEFAULT 0 FOR [ActualItemCount]; UPDATE [mig_tracking].[PartitionEventCount] SET [ActualCount] = 0 WHERE [ActualCount] IS NULL; IF EXISTS (SELECT TOP 1 1 FROM [mig_tracking].[PartitionEventCount]) BEGIN INSERT INTO [mig_tracking].[tmp_ms_xx_PartitionEventCount] ([MessageID], [EntityID], [PartitionValue], [AutomaticStateCode], [UserStateCode], [ActualCount], [BaselineCount], [RunDate]) SELECT [MessageID], [EntityID], [PartitionValue], [AutomaticStateCode], [UserStateCode], [ActualCount], [BaselineCount], [RunDate] FROM [mig_tracking].[PartitionEventCount] ORDER BY [MessageID] ASC, [EntityID] ASC, [PartitionValue] ASC; END ALTER TABLE [mig_tracking].[tmp_ms_xx_PartitionEventCount] DROP CONSTRAINT [SD_PartitionEventCount_abdb54bbcee24b9d8c49be301ad956a4]; DROP TABLE [mig_tracking].[PartitionEventCount]; EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_PartitionEventCount]', N'PartitionEventCount'; EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_constraint_PK_PartitionEventCount1]', N'PK_PartitionEventCount', N'OBJECT'; COMMIT TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO PRINT N'Starting rebuilding table [mig_tracking].[RootEntityEvent]...'; GO BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [mig_tracking].[tmp_ms_xx_RootEntityEvent] ( [EntityID] UNIQUEIDENTIFIER NOT NULL, [PartitionValue] VARCHAR (50) NOT NULL, [MessageID] UNIQUEIDENTIFIER NOT NULL, [ParameterHash] BINARY (20) NOT NULL, [EventCount] BIGINT NOT NULL, [ItemCount] BIGINT NOT NULL, [RunDate] DATETIME NOT NULL, CONSTRAINT [tmp_ms_xx_constraint_PK_RootEntityEvent1] PRIMARY KEY CLUSTERED ([EntityID] ASC, [PartitionValue] ASC, [MessageID] ASC, [ParameterHash] ASC) ); ALTER TABLE [mig_tracking].[tmp_ms_xx_RootEntityEvent] ADD CONSTRAINT [SD_RootEntityEvent_86ba9668e6dc40efa0c62522dcc14362] DEFAULT 0 FOR [ItemCount]; IF EXISTS (SELECT TOP 1 1 FROM [mig_tracking].[RootEntityEvent]) BEGIN INSERT INTO [mig_tracking].[tmp_ms_xx_RootEntityEvent] ([EntityID], [PartitionValue], [MessageID], [ParameterHash], [EventCount], [RunDate]) SELECT [EntityID], [PartitionValue], [MessageID], [ParameterHash], [EventCount], [RunDate] FROM [mig_tracking].[RootEntityEvent] ORDER BY [EntityID] ASC, [PartitionValue] ASC, [MessageID] ASC, [ParameterHash] ASC; END ALTER TABLE [mig_tracking].[tmp_ms_xx_RootEntityEvent] DROP CONSTRAINT [SD_RootEntityEvent_86ba9668e6dc40efa0c62522dcc14362]; DROP TABLE [mig_tracking].[RootEntityEvent]; EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_RootEntityEvent]', N'RootEntityEvent'; EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_constraint_PK_RootEntityEvent1]', N'PK_RootEntityEvent', N'OBJECT'; COMMIT TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO PRINT N'Altering [mig_tracking].[Baseline_SetCurrent]...'; GO ALTER procedure [mig_tracking].[Baseline_SetCurrent] @baselineId int as begin set nocount on; begin transaction; -- Clear current Baseline update [mig_tracking].[Baseline] set [IsCurrent] = 0 UPDATE [mig_tracking].[PartitionEventCount] set [BaselineCount] = NULL , [BaselineItemCount] = NULL update [mig_tracking].[EntityEventCount] set [BaselineCount] = NULL , [BaselineItemCount] = NULL update [mig_tracking].[Statistic] set [Available_Baseline] = NULL , [ExpRejected_Baseline] = NULL , [Exported_Baseline] = NULL , [ImpRejected_Baseline] = NULL , [Imported_Baseline] = NULL -- Restore baseline from Baseline update [mig_tracking].[Baseline] set [IsCurrent] = 1 where [BaselineId] = @baselineId; update [mig_tracking].[PartitionEventCount] set [BaselineCount] = B.[EventCount] , [BaselineItemCount] = B.[ItemCount] from [mig_tracking].[PartitionEventCount] as A inner join [mig_tracking].[BaselinePartitionEventCount] as B on B.[BaselineId] = @baselineId and B.[MessageID] = A.[MessageID] and B.[EntityID] = A.[EntityID] and B.[PartitionValue] = A.[PartitionValue] update [mig_tracking].[EntityEventCount] set [BaselineCount] = B.[BaselineCount] , [BaselineItemCount] = B.[BaselineItemCount] from [mig_tracking].[EntityEventCount] as A inner join ( select [MessageID] , [EntityID] , sum(isnull([BaselineCount],0)) AS [BaselineCount] , sum(isnull([BaselineItemCount],0)) AS [BaselineItemCount] FROM [mig_tracking].[PartitionEventCount] group by [MessageID] , [EntityID] ) as B on B.[MessageID] = A.[MessageID] and B.[EntityID] = A.[EntityID] update [mig_tracking].[Statistic] set [Available_Baseline] = B.[Available] , [ExpRejected_Baseline] = B.[ExpRejected] , [Exported_Baseline] = B.[Exported] , [ImpRejected_Baseline] = B.[ImpRejected] , [Imported_Baseline] = B.[Imported] from [mig_tracking].[Statistic] as A inner join [mig_tracking].[BaselineStatistic] as B on A.[PartitionValue] = B.[PartitionValue] and A.[EntityID] = B.[EntityID] where B.[BaselineId] = @baselineId; commit end GO PRINT N'Altering [mig_tracking].[Actual_Update]...'; GO ALTER PROCEDURE [mig_tracking].[Actual_Update] @systemMessages xml -- , @userMessages xml -- , @entitiesToProcess varchar(max) = null , @runDate datetime , @user varchar(256) , @jobInstanceId int as begin set nocount on; declare @start datetime = getdate() declare @logId bigint declare @text varchar(max) = '------------------------------------------------------------------ Elapsed [mig_tracking].[Actual_Update] ------------------------------------------------------------------' exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output -- Table to hold EntityID to process declare @entities table(EntityID uniqueidentifier, primary key (EntityID)) if @entitiesToProcess is not null begin insert into @entities select distinct [Id] from [mig_utility].[ParseGuids](@entitiesToProcess, ',') end -- Table to hold known User Events from project database declare @userEvents table([MessageID] uniqueidentifier, [Step] tinyint, [Engine] tinyint, [Severity] int, [Receiver] int, [EventID] uniqueidentifier); with msgXml as ( select T.c.query('.') as [Msg] from @userMessages.nodes('/list/msg') T(c) ) insert @userEvents select [Msg].value('(*/messageID)[1]', 'uniqueidentifier') as [MessageID] , [Msg].value('(*/step)[1]', 'tinyint') as [Step] , [Msg].value('(*/engine)[1]', 'tinyint') as [Engine] , [Msg].value('(*/severity)[1]', 'int') as [Severity] , [Msg].value('(*/receiver)[1]', 'int') as [Receiver] , [Msg].value('(*/eventID)[1]', 'uniqueidentifier') as [EventID] from msgXml -- Table to hold known System Events from project database declare @systemEvents table([MessageID] uniqueidentifier, [Step] tinyint, [Engine] tinyint, [Severity] int, [Receiver] int, [MessageFmt] varchar(max)); with msgXml as ( select T.c.query('.') as [Msg] from @systemMessages.nodes('/list/msg') T(c) ) insert @systemEvents select [Msg].value('(*/messageID)[1]', 'uniqueidentifier') as [MessageID] , [Msg].value('(*/step)[1]', 'tinyint') as [Step] , [Msg].value('(*/engine)[1]', 'tinyint') as [Engine] , [Msg].value('(*/severity)[1]', 'int') as [Severity] , [Msg].value('(*/receiver)[1]', 'int') as [Receiver] , [Msg].value('(*/messageFmt)[1]', 'varchar(max)') as [MessageFmt] from msgXml ----------------------------------------------------- -- RootItem ----------------------------------------------------- if @entitiesToProcess is null begin if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_SourceKey') drop index [IX_RootItem_SourceKey] on [mig_tracking].[RootItem] if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_MigrationKey') drop index [IX_RootItem_MigrationKey] on [mig_tracking].[RootItem] if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_TargetKey') drop index [IX_RootItem_TargetKey] on [mig_tracking].[RootItem] truncate table [mig_tracking].[RootItem] end else begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItem', @logId output delete [mig_tracking].[RootItem] from [mig_tracking].[RootItem] as A inner join @entities as B on A.[EntityID] = B.[EntityID] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItem', @logId output insert [mig_tracking].[RootItem] select [ItemId] , [PartitionValue] , [EntityID] , [SourceKey] , [MigrationKey] , [TargetKey] , [IsExported] , [IsImported] , [ExportID] , [HasSourceXml] , [HasExportXml] , [HasTargetXml] , @runDate from [mig_tracking].[VwRootItem] where (@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities)) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_SourceKey') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create SourceKey index on RootItem', @logId output create nonclustered index [IX_RootItem_SourceKey] on [mig_tracking].[RootItem] ( [EntityID] asc , [SourceKey] asc , [PartitionValue] asc ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_MigrationKey') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create MigrationKey index on RootItem', @logId output create nonclustered index [IX_RootItem_MigrationKey] on [mig_tracking].[RootItem] ( [EntityID] asc , [MigrationKey] asc , [PartitionValue] asc ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItem]') and name = N'IX_RootItem_TargetKey') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create TargetKey index on RootItem', @logId output create nonclustered index [IX_RootItem_TargetKey] on [mig_tracking].[RootItem] ( [EntityID] asc , [TargetKey] asc , [PartitionValue] asc ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end ----------------------------------------------------- -- RootItemLink ----------------------------------------------------- if @entitiesToProcess is null begin if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetItemId') drop index [IX_RootItemLink_TargetItemId] on [mig_tracking].[RootItemLink] if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetEntityID') drop index [IX_RootItemLink_TargetEntityID] on [mig_tracking].[RootItemLink] truncate table [mig_tracking].[RootItemLink] end else begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItemLink', @logId output delete [mig_tracking].[RootItemLink] from [mig_tracking].[RootItemLink] as A inner join @entities as B on A.[TargetEntityID] = B.[EntityID] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemLink', @logId output insert [mig_tracking].[RootItemLink] select [ParentItemId] , [ChildItemId] , [ChildEntityID] , @runDate from [mig_tracking].[VwRootItemLink] where (@entitiesToProcess is null or [ChildEntityID] in (select [EntityID] from @entities)) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetItemId') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create TargetItemId index on RootItemLink', @logId output create nonclustered index [IX_RootItemLink_TargetItemId] on [mig_tracking].[RootItemLink] ( [TargetItemId] asc ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end if not exists (select * from sys.indexes WHERE object_id = object_id(N'[mig_tracking].[RootItemLink]') and name = N'IX_RootItemLink_TargetEntityID') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create TargetEntityID index on RootItemLink', @logId output create nonclustered index [IX_RootItemLink_TargetEntityID] ON [mig_tracking].[RootItemLink] ( [TargetEntityID] asc ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end ----------------------------------------------------- -- EventParameter ----------------------------------------------------- exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert EventParameter', @logId output ;with usrEv as (select distinct [ParameterHash], [ParameterXml] from [mig_valid].[EventUser]) , sysEv as (select distinct [ParameterHash], [ParameterXml] from [mig_valid].[EventSystem]) , allEv as ( select * from usrEv union select * from sysEv ) insert [mig_tracking].[EventParameter] ( [HashValue] , [ParameterXml] , [RunDate] ) select A.[ParameterHash] , A.[ParameterXml] , @runDate from allEv as A left outer join [mig_tracking].[EventParameter] as B on B.[HashValue] = A.[ParameterHash] where B.[HashValue] is null exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 ----------------------------------------------------- -- RootItemEvent ----------------------------------------------------- if @entitiesToProcess is null begin if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID') drop index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent] if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent') drop index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent] if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId') drop index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent] truncate table [mig_tracking].[RootItemEvent] end else begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItemEvent', @logId output delete [mig_tracking].[RootItemEvent] from [mig_tracking].[RootItemEvent] as A inner join @entities as B on A.[EntityID] = B.[EntityID] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end -- User events exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (User)', @logId output ;with [events] as ( select B.[EntityID] , C.[PartitionValue] , A.[ItemId] , A.[Step] , A.[Engine] , A.[Receiver] , A.[Severity] , A.[EventID] , A.[ParameterHash] , A.[ParameterXml] , count(*) AS [EventCount] from [mig_valid].[EventUser] as A inner join [mig_item].[ExportKey] as B on A.[ItemId] = B.[ItemId] inner join [mig_item].[Item] as C on A.[ItemId] = C.[ItemId] where (@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities)) group by B.[EntityID] , C.[PartitionValue] , A.[ItemId] , A.[Engine] , A.[Step] , A.[Receiver] , A.[Severity] , A.[EventID] , A.[ParameterHash] , A.[ParameterXml] ) insert [mig_tracking].[RootItemEvent] ( [RowID] , [EntityID] , [ItemId] , [PartitionValue] , [MessageID] , [ParameterHash] , [EventCount] , [RunDate]) select newid() -- To detect problem with join , A.[EntityID] , A.[ItemId] , A.[PartitionValue] , B.[MessageID] , A.[ParameterHash] , A.[EventCount] , @runDate from [events] as A inner join @userEvents as B on B.[Step] = A.[Step] and B.[Engine] = A.[Engine] and B.[Severity] = A.[Severity] and B.[Receiver] = A.[Receiver] and B.[EventID] = A.[EventID] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 -- System events exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (System)', @logId output ;with [events] as ( select B.[EntityID] , C.[PartitionValue] , A.[ItemId] , A.[Step] , A.[Engine] , A.[Receiver] , A.[Severity] , A.[Message] , A.[ParameterHash] , A.[ParameterXml] , count(*) AS [EventCount] from [mig_valid].[EventSystem] as A inner join [mig_item].[ExportKey] as B on A.[ItemId] = B.[ItemId] inner join [mig_item].[Item] as C on A.[ItemId] = C.[ItemId] where (@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities)) group by B.[EntityID] , C.[PartitionValue] , A.[ItemId] , A.[Engine] , A.[Step] , A.[Receiver] , A.[Severity] , A.[Message] , A.[ParameterHash] , A.[ParameterXml] ) insert [mig_tracking].[RootItemEvent] ( [RowID] , [EntityID] , [ItemId] , [PartitionValue] , [MessageID] , [ParameterHash] , [EventCount] , [RunDate]) select newid() -- To detect problem with join , A.[EntityID] , A.[ItemId] , A.[PartitionValue] , B.[MessageID] , A.[ParameterHash] , A.[EventCount] , @runDate from [events] as A inner join @systemEvents as B on B.[Step] = A.[Step] and B.[Engine] = A.[Engine] and B.[Severity] = A.[Severity] and B.[Receiver] = A.[Receiver] and B.[MessageFmt] = A.[Message] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create EntityID index on RootItemEvent', @logId output create nonclustered index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent] ( [EntityID] asc ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent index on RootItemEvent', @logId output create nonclustered index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent] ( [MessageID] asc, [EntityID] asc, [PartitionValue] asc, [ItemId] asc ) include ( [ParameterHash] ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId') begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent_ItemId index on RootItemEvent', @logId output create nonclustered index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent] ( [ItemId] asc ) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end ----------------------------------------------------- -- RootEntityEvent ----------------------------------------------------- if @entitiesToProcess is null begin truncate table [mig_tracking].[RootEntityEvent]; end else begin exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootEntityEvent', @logId output delete [mig_tracking].[RootEntityEvent] from [mig_tracking].[RootEntityEvent] as A inner join @entities as B on A.[EntityID] = B.[EntityID] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 end exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootEntityEvent', @logId output insert [mig_tracking].[RootEntityEvent] ( [EntityID] , [PartitionValue] , [MessageID] , [ParameterHash] , [EventCount] , [ItemCount] , [RunDate]) select [EntityID] , [PartitionValue] , [MessageID] , [ParameterHash] , sum([EventCount]) , count(distinct [ItemId]) , @runDate from [mig_tracking].[RootItemEvent] where (@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities)) group by [EntityID] , [PartitionValue] , [MessageID] , [ParameterHash] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 -- Table variable to hold modified UserStates for the next 2 steps. Used to log UserStates changes declare @modifiedUserStates table( [MessageID] uniqueidentifier not null , [EntityID] uniqueidentifier not null , [PartitionValue] nvarchar(50) null , [UserStateCode] int not null , [ActualCount] int null , index ix1 ([MessageID], [EntityID]) , index ix2 ([MessageID], [EntityID], [PartitionValue]) ) declare @entries int declare @userStateTranslation table([UserStateCode] int, [Label] nvarchar(50), primary key ([UserStateCode])) insert @userStateTranslation select [UserStateCode] , [Label] from [mig_tracking].[UserState] ----------------------------------------------------- -- PartitionEventCount ----------------------------------------------------- exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, select', @logId output select [MessageID] , [EntityID] , [PartitionValue] , sum([EventCount]) as [ActualCount] , sum([ItemCount]) as [ActualItemCount] into #partitionEventCount from [mig_tracking].[RootEntityEvent] where (@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities)) group by [MessageID] , [EntityID] , [PartitionValue] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, update', @logId output ;with [events] as ( select A.[MessageID] , A.[EntityID] , A.PartitionValue , A.[ActualCount] , A.[ActualItemCount] , [mig_tracking].[ufGetAutomaticState](A.[ActualCount], B.[BaselineCount]) as [AutomaticStateCode] from #partitionEventCount as A inner join [mig_tracking].[PartitionEventCount] as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] and A.[PartitionValue] = B.[PartitionValue] ) update [mig_tracking].[PartitionEventCount] set [ActualCount] = isnull(B.[ActualCount], 0) , [ActualItemCount] = isnull(B.[ActualItemCount], 0) , [UserStateCode] = isnull(C.[NewUserStateCode], A.[UserStateCode]) , [AutomaticStateCode] = isnull(B.[AutomaticStateCode], [mig_tracking].[ufGetAutomaticState](isnull(B.[ActualCount], 0), A.[BaselineCount])) , [RunDate] = @runDate output deleted.[MessageID], deleted.[EntityID], deleted.[PartitionValue], deleted.[UserStateCode], deleted.[ActualCount] into @modifiedUserStates from [mig_tracking].[PartitionEventCount] as A left outer join [events] as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] and A.[PartitionValue] = B.[PartitionValue] left outer join [mig_tracking].[UserStateUpdate] as C on C.[UserStateCode] = A.[UserStateCode] and C.[AutomaticStateCode] = isnull(B.[AutomaticStateCode], [mig_tracking].[ufGetAutomaticState](0, A.[BaselineCount])) where (@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities)) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, insert', @logId output ;with [events] as ( select A.[MessageID] , A.[EntityID] , A.[PartitionValue] , A.[ActualCount] , A.[ActualItemCount] , 0 as [UserStateCode] , [mig_tracking].[ufGetAutomaticState](A.[ActualCount], null) as [AutomaticStateCode] from #partitionEventCount as A left outer join [mig_tracking].[PartitionEventCount] as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] and A.[PartitionValue] = B.[PartitionValue] where B.[MessageID] is null ) insert [mig_tracking].[PartitionEventCount] ( [MessageID] , [EntityID] , [PartitionValue] , [AutomaticStateCode] , [UserStateCode] , [ActualCount] , [BaselineCount] , [ActualItemCount] , [BaselineItemCount] , [RunDate]) select A.[MessageID] , A.[EntityID] , A.[PartitionValue] , A.[AutomaticStateCode] , isnull(B.[NewUserStateCode], A.[UserStateCode]) , A.[ActualCount] , null , A.[ActualItemCount] , null , @runDate from [events] as A left outer join [mig_tracking].[UserStateUpdate] as B on B.[UserStateCode] = A.[UserStateCode] and B.[AutomaticStateCode] = A.[AutomaticStateCode] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 -- Log UserState changes exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, history', @logId output insert [mig_tracking].[Log] select A.[MessageID] , A.[EntityID] , A.[PartitionValue] , case when B.[UserStateCode] is null then 0 -- Added else 1 -- Modified end , @runDate , @user , 'User State' , bef.[Label] , aft.[Label] from [mig_tracking].[PartitionEventCount] as A left outer join @modifiedUserStates as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] and A.[PartitionValue] = B.[PartitionValue] left outer join @userStateTranslation as bef on B.[UserStateCode] = bef.[UserStateCode] left outer join @userStateTranslation as aft on A.[UserStateCode] = aft.[UserStateCode] where A.[UserStateCode] <> B.[UserStateCode] set @entries = @@rowCount -- Log count changes insert [mig_tracking].[Log] select coalesce(A.[MessageID], B.[MessageID]) , coalesce(A.[EntityID], B.[EntityID]) , coalesce(A.[PartitionValue], B.[PartitionValue]) , case when B.[ActualCount] is null then 0 -- Added else 1 -- Modified end , @runDate , @user , 'Count' , B.[ActualCount] , isnull(A.[ActualCount], 0) from [mig_tracking].[PartitionEventCount] as A full outer join @modifiedUserStates as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] and A.[PartitionValue] = B.[PartitionValue] where A.[ActualCount] <> B.[ActualCount] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 drop table #partitionEventCount ----------------------------------------------------- -- EntityEventCount ----------------------------------------------------- delete @modifiedUserStates exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'EntityEventCount, select', @logId output select [MessageID] , [EntityID] , sum([EventCount]) as [ActualCount] , sum([ItemCount]) as [ActualItemCount] into #entityEventCount from [mig_tracking].[RootEntityEvent] where (@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities)) group by [MessageID] , [EntityID] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'EntityEventCount, update', @logId output ;with [events] as ( select A.[MessageID] , A.[EntityID] , A.[ActualCount] , A.[ActualItemCount] , [mig_tracking].[ufGetAutomaticState](A.[ActualCount], B.[BaselineCount]) as [AutomaticStateCode] from #entityEventCount as A inner join [mig_tracking].[EntityEventCount] as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] ) update [mig_tracking].[EntityEventCount] set [ActualCount] = isnull(B.[ActualCount], 0) , [ActualItemCount] = isnull(B.[ActualItemCount], 0) , [UserStateCode] = isnull(C.[NewUserStateCode], A.[UserStateCode]) , [AutomaticStateCode] = isnull(B.[AutomaticStateCode], [mig_tracking].[ufGetAutomaticState](isnull(B.[ActualCount], 0), A.[BaselineCount])) , [RunDate] = @runDate output deleted.[MessageID], deleted.[EntityID], null, deleted.[UserStateCode], deleted.[ActualCount] into @modifiedUserStates from [mig_tracking].[EntityEventCount] as A left outer join [events] as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] left outer join [mig_tracking].[UserStateUpdate] as C on C.[UserStateCode] = A.[UserStateCode] and C.[AutomaticStateCode] = isnull(B.[AutomaticStateCode], [mig_tracking].[ufGetAutomaticState](0, A.[BaselineCount])) where (@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities)) exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'EntityEventCount, insert', @logId output ;with [events] as ( select A.[MessageID] , A.[EntityID] , A.[ActualCount] , A.[ActualItemCount] , 0 as [UserStateCode] , [mig_tracking].[ufGetAutomaticState](A.[ActualCount], null) as [AutomaticStateCode] from #entityEventCount as A left outer join [mig_tracking].[EntityEventCount] as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] where B.[MessageID] is null ) insert [mig_tracking].[EntityEventCount] ( [MessageID] , [EntityID] , [AutomaticStateCode] , [UserStateCode] , [ActualCount] , [BaselineCount] , [ActualItemCount] , [BaselineItemCount] , [RunDate]) select A.[MessageID] , A.[EntityID] , A.[AutomaticStateCode] , isnull(B.[NewUserStateCode], A.[UserStateCode]) , A.[ActualCount] , null , A.[ActualItemCount] , null , @runDate from [events] as A left outer join [mig_tracking].[UserStateUpdate] as B on B.[UserStateCode] = A.[UserStateCode] and B.[AutomaticStateCode] = A.[AutomaticStateCode] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 -- Log UserState changes exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'EntityEventCount, history', @logId output insert [mig_tracking].[Log] select A.[MessageID] , A.[EntityID] , null , case when B.[UserStateCode] is null then 0 -- Added else 1 -- Modified end , @runDate , @user , 'User State' , bef.[Label] , aft.[Label] from [mig_tracking].[EntityEventCount] as A left outer join @modifiedUserStates as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] left outer join @userStateTranslation as bef on B.[UserStateCode] = bef.[UserStateCode] left outer join @userStateTranslation as aft on A.[UserStateCode] = aft.[UserStateCode] where A.[UserStateCode] <> B.[UserStateCode] set @entries = @@rowCount -- Log count changes insert [mig_tracking].[Log] select coalesce(A.[MessageID], B.[MessageID]) , coalesce(A.[EntityID], B.[EntityID]) , null , case when B.[ActualCount] is null then 0 -- Added else 1 -- Modified end , @runDate , @user , 'Count' , B.[ActualCount] , isnull(A.[ActualCount], 0) from [mig_tracking].[EntityEventCount] as A full outer join @modifiedUserStates as B on A.[MessageID] = B.[MessageID] and A.[EntityID] = B.[EntityID] where A.[ActualCount] <> B.[ActualCount] exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 drop table #entityEventCount ----------------------------------------------------- -- Statistics ----------------------------------------------------- exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Statistics', @logId output select * into #export from [mig_director].[_Export_Statistics]() where [PartitionValue] is not null select * into #import from [mig_director].[_Import_Statistics]() where [PartitionValue] is not null select A.[PartitionValue] , A.[EntityID] , A.[EntityName] , A.[BusinessEntityName] , A.[Count] - A.[Dropped] as [Available] , A.[Rejected] as [ExpRejected] , A.[Exported] , isnull(B.[Rejected], 0) as [ImpRejected] , isnull(B.[Imported], 0) as [Imported] , A.[Difference] + isnull(B.[Difference], 0) as [Check] into #statistics from #export as A left outer join #import as B on A.[PartitionValue] = B.[PartitionValue] and A.[EntityID] = B.[EntityID] where (@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities)) order by A.[EntityName] -- Update Obsolete and Reoccurring update [mig_tracking].[Statistic] set [EntityName] = isnull(B.[EntityName], A.[EntityName]) , [BusinessEntityName] = isnull(B.[BusinessEntityName], A.[BusinessEntityName]) , [Available] = isnull(B.[Available], 0) , [ExpRejected] = isnull(B.[ExpRejected], 0) , [Exported] = isnull(B.[Exported], 0) , [ImpRejected] = isnull(B.[ImpRejected], 0) , [Imported] = isnull(B.[Imported], 0) from [mig_tracking].[Statistic] as A left outer join #statistics as B on A.[PartitionValue] = B.[PartitionValue] and A.[EntityID] = B.[EntityID] where (@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities)) -- Insert new insert [mig_tracking].[Statistic] ( [PartitionValue] , [EntityID] , [BusinessEntityName] , [EntityName] , [Available] , [ExpRejected] , [Exported] , [ImpRejected] , [Imported] ) select A.[PartitionValue] , A.[EntityID] , A.[BusinessEntityName] , A.[EntityName] , A.[Available] , A.[ExpRejected] , A.[Exported] , A.[ImpRejected] , A.[Imported] from #statistics as A left outer join [mig_tracking].[Statistic] as B on A.[PartitionValue] = B.[PartitionValue] and A.[EntityID] = B.[EntityID] where B.[EntityID] is null exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 ----------------------------------------------------- -- Parameters ----------------------------------------------------- exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Parameters', @logId output truncate table [mig_tracking].[Parameter] insert [mig_tracking].[Parameter] select [ItemID] , [ProjectType] , [Name] , [Value] , [Description] from [mig_director].[RunParameter] where [Present] = 1 and [Value] is not null exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0 set @text = '------------------------------------------------------------------ ' + convert(varchar, getdate() - @start, 108) + ' Total ------------------------------------------------------------------' exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output END GO PRINT N'Altering [mig_tracking].[Baseline_Save]...'; GO ALTER procedure [mig_tracking].[Baseline_Save] @title nvarchar(128) , @comment nvarchar(max) , @setCurrent bit as begin set nocount on; begin transaction -- Create Baseline insert [mig_tracking].[Baseline]( [Title] , [Comment] , [IsCurrent] ) values ( @title , @comment , 0 ); declare @baselineId int = scope_identity() -- Save data insert [mig_tracking].[BaselinePartitionEventCount]( [BaselineId] , [MessageID] , [EntityID] , [PartitionValue] , [EventCount] , [ItemCount] ) select @baselineId , [MessageID] , [EntityID] , [PartitionValue] , [ActualCount] , [ActualItemCount] from [mig_tracking].[PartitionEventCount]; insert [mig_tracking].[BaselineStatistic] ( [BaselineId] , [PartitionValue] , [EntityID] , [Available] , [ExpRejected] , [Exported] , [ImpRejected] , [Imported] ) select @baselineId , [PartitionValue] , [EntityID] , [Available] , [ExpRejected] , [Exported] , [ImpRejected] , [Imported] from [mig_tracking].[Statistic] if 1 = @setCurrent begin exec [mig_tracking].[Baseline_SetCurrent] @baselineId end commit end GO PRINT N'Refreshing [mig_director].[Setup_ClearOther]...'; GO EXECUTE sp_refreshsqlmodule N'[mig_director].[Setup_ClearOther]'; GO PRINT N'Refreshing [mig_tracking].[Baseline_Delete]...'; GO EXECUTE sp_refreshsqlmodule N'[mig_tracking].[Baseline_Delete]'; GO PRINT N'Update complete.'; GO