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