ALTER procedure [mig_director].[Job_ListEvent]
@jobId int
, @lowestSeverity int
as
with
systemEvents as (
select
B.[EntityID]
, A.[Severity]
, A.[Receiver]
, A.[Message]
, count(*) as [Count]
, count(distinct A.[ItemId]) as [Items]
from
[mig_item].[EventSystem] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
A.[JobId] = @jobId and A.[Severity] <= @lowestSeverity
group by
B.[EntityID]
, A.[Severity]
, A.[Receiver]
, A.[Message]
)
, userEvents as (
select
B.[EntityID]
, A.[EventID]
, A.[EventName]
, A.[Severity]
, A.[Receiver]
, A.[EventTextID]
, count(*) as [Count]
, count(distinct A.[ItemId]) as [Items]
from
[mig_item].[EventUser] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
A.[JobId] = @jobId and A.[Severity] <= @lowestSeverity
group by
B.[EntityID]
, A.[EventID]
, A.[EventName]
, A.[Severity]
, A.[Receiver]
, A.[EventTextID]
)
, texts as (
select
[ItemID] as [TextID]
, substring([DefaultText], 1, 2000) as [Text]
from [mig_director].[SmartText] with (NoLock)
)
, list as (
select
[EntityID]
, null as [EventID]
, [Severity]
, [Receiver]
, '(System)' as [EventName]
, [Message]
, [Count]
, [Items]
from systemEvents
union all
select
[EntityID]
, [EventID]
, A.[Severity]
, A.[Receiver]
, A.[EventName]
, isnull(B.[Text], 'Text not found (' + lower(convert(varchar(50), A.[EventTextID])) + ')') as [Message]
, A.[Count]
, A.[Items]
from
userEvents as A
left outer join texts as B
on A.[EventTextID] = B.[TextID]
)
select
B.[EntityId]
, B.[EntityName]
, C.[BusinessEntityName]
, A.[EventID]
, A.[Severity]
, A.[Receiver]
, A.[EventName]
, A.[Message]
, A.[Count]
, A.[Items]
from
list as A
inner join [mig_director].[ExportEntity] as B
on A.[EntityID] = B.[EntityId]
inner join [mig_director].[BusinessEntity] as C
on B.[BusinessEntityID] = C.[BusinessEntityID]
order by
B.[EntityName]
, A.[EventName]
, A.[Message]
, A.[Severity]
, A.[Receiver]
go
ALTER procedure [mig_director].[Job_ListEventParameter]
@jobId int
, @limit int
, @entityID uniqueidentifier
, @message varchar(max)
, @eventID uniqueidentifier = null
, @severity int
, @receiver int
as
with
itemCount as (
-- EITHER User Events part
select top(@limit)
A.[ParameterHash]
, A.[ItemId]
, 0 as [Type]
, count(*) as [Count]
from
[mig_item].[EventUser] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on
A.[ItemId] = B.[ItemId]
where
@eventID is not null
and A.[JobId] = @jobId
and A.[EventID] = @eventID
and B.[EntityID] = @entityID
and A.[Severity] = @severity
and A.[Receiver] = @receiver
group by
A.[ParameterHash]
, A.[ItemId]
union all
-- OR System Events part
select top(@limit)
A.[ParameterHash]
, A.[ItemId]
, 1 as [Type]
, count(*) as [Count]
from
[mig_item].[EventSystem] as A with (NoLock)
inner join [mig_item].[ExportKey] as B with (NoLock)
on A.[ItemId] = B.[ItemId]
where
@eventID is null
and A.[JobId] = @jobId
and A.[Message] = @message
and B.[EntityID] = @entityID
and A.[Severity] = @severity
and A.[Receiver] = @receiver
group by
A.[ParameterHash]
, A.[ItemId]
)
, hashCount as (
select
[ParameterHash]
, [Type]
, sum([Count]) as [Count]
, count(*) as [Items]
from
itemCount
group by
[ParameterHash]
, [Type]
)
select
[ParameterXml] = case A.[Type]
when 0 then (
select
top(1) [ParameterXml]
from [mig_item].[EventUser] as B with (NoLock)
where
B.[ParameterHash] = A.[ParameterHash]
and B.[JobId] = @jobId
and B.[EventID] = @eventID
)
else (
select
top(1) [ParameterXml]
from [mig_item].[EventSystem] as B with (NoLock)
where
B.[ParameterHash] = A.[ParameterHash]
and B.[JobId] = @jobId
and B.[Message] = @message
)
end
, A.[Count]
, A.[Items]
from
hashCount as A
go
ALTER PROCEDURE [mig_director].[Export_SourceTable_InsertOrUpdate]
@itemID uniqueidentifier
, @tableSchema varchar(50)
, @tableName varchar(50)
, @wasCreated bit
AS
update [mig_director].[ExportSourceTable]
set
[TableSchema] = @tableSchema
, [LoadedCount] = case @wasCreated
when 1 then null
else [LoadedCount]
end
, [IsPresent] = 1
, [ItemID] = @itemID
where
[ItemID] = @itemID
if 0 = @@rowCount
begin
declare @reformatExtensionID uniqueidentifier
select top(1)
@reformatExtensionID = [ID]
from
[mig_director].[ExtensionUsage]
where
[ExtensionType] = 1 -- 1: Reformatter
and [IsDefault] = 1
insert [mig_director].[ExportSourceTable] (
[ItemID]
, [TableSchema]
, [TableName]
, [ReformatExtensionID]
, [LoadedCount]
, [IsPresent]
) values (
@itemID
, @tableSchema
, @tableName
, @reformatExtensionID
, null
, 1
)
end
if 1 = @wasCreated
begin
update [mig_director].[ExportFormattedFile]
set
[LoadedCount] = 0
, [LoadedDate] = null
, [DateOfLoadedFile] = null
from
[mig_director].[ExportFormattedFile] as A
inner join [mig_director].[ExportSourceFile] as B
on
A.[FileName] = B.[FileName]
and B.[SourceTableFullName] = @tableSchema + '.' + @tableName
end
GO
alter procedure [mig_translation].[ListValuesets]
as
select
[ItemID] as [id]
, [Name] as [name]
, [Columns].value('(*/@displaySmartTextId)[1]', 'uniqueidentifier') as [displayTextID]
, [Columns].value('(*/@descriptionSmartTextId)[1]', 'uniqueidentifier') as [descriptionTextID]
from [mig_director].[Valueset]
where
[ValuesetType] = 2
and [Present] = 1
for xml path('valueset'), root('list')
go
PRINT 'Dropping table [mig_tracking].[BaselineStatistic]...'
drop table if exists [mig_tracking].[BaselineStatistic]
go
PRINT N'Starting rebuilding table [mig_tracking].[Statistic]...';
GO
BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [mig_tracking].[tmp_ms_xx_Statistic] (
[PartitionValue] VARCHAR (50) NOT NULL,
[EntityID] UNIQUEIDENTIFIER NOT NULL,
[Time] DATETIME2 (7) NOT NULL,
[Available] BIGINT NOT NULL,
[ExpRejected] BIGINT NOT NULL,
[Exported] BIGINT NOT NULL,
[ImpRejected] BIGINT NOT NULL,
[Imported] BIGINT NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_Statistic1] PRIMARY KEY CLUSTERED ([PartitionValue] ASC, [EntityID] ASC, [Time] ASC)
);
IF EXISTS (SELECT TOP 1 1 FROM [mig_tracking].[Statistic])
BEGIN
declare @time datetime2(7) = (select isnull(max([Time]), sysdatetime()) from [mig_tracking].[PartitionEventCount])
INSERT INTO [mig_tracking].[tmp_ms_xx_Statistic] (
[PartitionValue]
, [EntityID]
, [Available]
, [ExpRejected]
, [Exported]
, [ImpRejected]
, [Imported]
, [Time]
)
SELECT [PartitionValue],
[EntityID],
[Available],
[ExpRejected],
[Exported],
[ImpRejected],
[Imported],
@time
FROM [mig_tracking].[Statistic]
ORDER BY [PartitionValue] ASC, [EntityID] ASC;
END
DROP TABLE [mig_tracking].[Statistic];
EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_Statistic]', N'Statistic';
EXECUTE sp_rename N'[mig_tracking].[tmp_ms_xx_constraint_PK_Statistic1]', N'PK_Statistic', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
PRINT N'Altering Procedure [mig_tracking].[Actual_Update]...';
GO
ALTER procedure [mig_tracking].[Actual_Update]
@systemMessages xml --
, @userMessages xml --
, @entitiesToProcess varchar(max) = null
, @runDate datetime2
, @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]
, [SourceCommit]
, [SourceCleanWorkspace]
, [TargetCommit]
, [TargetCleanWorkspace]
, [ExportJobId]
, [ExportTime]
, [ImportJobId]
, [ImportTime]
, @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], [GroupHash], [ParameterXml] from [mig_valid].[EventUser])
, sysEv as (select distinct [ParameterHash], [GroupHash], [ParameterXml] from [mig_valid].[EventSystem])
, allEv as (
select * from usrEv
union
select * from sysEv
)
insert [mig_tracking].[EventParameter] (
[HashValue]
, [GroupHash]
, [ParameterXml]
, [RunDate]
)
select
A.[ParameterHash]
, A.[GroupHash]
, 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.[GroupHash]
, 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.[GroupHash]
, A.[ParameterXml]
)
insert [mig_tracking].[RootItemEvent] (
[RowID]
, [EntityID]
, [ItemId]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
, [EventCount]
, [RunDate])
select
newid() -- To detect problem with join
, A.[EntityID]
, A.[ItemId]
, A.[PartitionValue]
, B.[MessageID]
, A.[ParameterHash]
, A.[GroupHash]
, 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.[GroupHash]
, 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.[GroupHash]
, A.[ParameterXml]
)
insert [mig_tracking].[RootItemEvent] (
[RowID]
, [EntityID]
, [ItemId]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
, [EventCount]
, [RunDate])
select
newid() -- To detect problem with join
, A.[EntityID]
, A.[ItemId]
, A.[PartitionValue]
, B.[MessageID]
, A.[ParameterHash]
, A.[GroupHash]
, 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]
, [GroupHash]
, [EventCount]
, [ItemCount]
, [RunDate])
select
[EntityID]
, [PartitionValue]
, [MessageID]
, [ParameterHash]
, [GroupHash]
, 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]
, [GroupHash]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
-----------------------------------------------------
-- PartitionEventCount
-----------------------------------------------------
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, update', @logId output
declare @addedCounts table(
[MessageID] uniqueidentifier not null
, [EntityID] uniqueidentifier not null
, [PartitionValue] nvarchar(50) not null
, [InstanceCount] int not null
, [ItemCount] int not null
)
declare @existingCounts table(
[MessageID] uniqueidentifier not null
, [EntityID] uniqueidentifier not null
, [PartitionValue] nvarchar(50) not null
, [InstanceCount] int not null
, [ItemCount] int not null
)
insert @existingCounts
select
A.[MessageID]
, A.[EntityID]
, A.[PartitionValue]
, A.[InstanceCount]
, A.[ItemCount]
from
[mig_tracking].[PartitionEventCount] as A
where
A.[Time] = (
select max([Time])
from [mig_tracking].[PartitionEventCount] as B
where
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
)
;with
[newCounts] as (
select
[MessageID]
, [EntityID]
, [PartitionValue]
, sum([EventCount]) as [InstanceCount]
, count(distinct [ItemId]) as [ItemCount]
from
[mig_tracking].[RootItemEvent]
where
(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
group by
[MessageID]
, [EntityID]
, [PartitionValue]
)
insert [mig_tracking].[PartitionEventCount] (
[MessageID]
, [EntityID]
, [PartitionValue]
, [InstanceCount]
, [ItemCount]
, [Time]
)
output inserted.[MessageID], inserted.[EntityID], inserted.[PartitionValue], inserted.[InstanceCount], inserted.[ItemCount] into @addedCounts
select
coalesce(A.[MessageID], B.[MessageID])
, coalesce(A.[EntityID], B.[EntityID])
, coalesce(A.[PartitionValue], B.[PartitionValue])
, isnull(A.[InstanceCount], 0)
, isnull(A.[ItemCount], 0)
, @runDate
from
[newCounts] as A
full outer join @existingCounts as B
on
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
where
(@entitiesToProcess is null or coalesce(A.[EntityID], B.[EntityID]) in (select [EntityID] from @entities))
and (
isnull(A.[InstanceCount], 0) <> isnull(B.[InstanceCount], 0)
or isnull(A.[ItemCount], 0) <> isnull(B.[ItemCount], 0)
)
-- Log count changes
insert [mig_tracking].[Log]
select
A.[MessageID]
, A.[EntityID]
, A.[PartitionValue]
, case
when B.[InstanceCount] is null then 0 -- Added
else 1 -- Modified
end
, @runDate
, @user
, 'Instance Count (' + A.[PartitionValue] + ')'
, format(isnull(B.[InstanceCount], 0), 'N0')
, format(A.[InstanceCount], 'N0')
from
@addedCounts as A
left outer join @existingCounts as B
on
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
-----------------------------------------------------
-- Statistics
-----------------------------------------------------
exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Statistics', @logId output;
with
export as (
select *
from [mig_director].[_Export_Statistics]()
where
[PartitionValue] is not null
)
, import as (
select *
from [mig_director].[_Import_Statistics]()
where
[PartitionValue] is not null
)
, newStatistic as (
select
A.[PartitionValue]
, A.[EntityID]
, 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]
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))
)
, curStatistic as (
select *
from
[mig_tracking].[Statistic] as A
where
A.[Time] = (
select max([Time])
from [mig_tracking].[Statistic]
where
[PartitionValue] = A.[PartitionValue]
and [EntityID] = A.[EntityID]
)
)
insert [mig_tracking].[Statistic] (
[PartitionValue]
, [EntityID]
, [Time]
, [Available]
, [ExpRejected]
, [Exported]
, [ImpRejected]
, [Imported]
)
select
A.[PartitionValue]
, A.[EntityID]
, @runDate
, A.[Available]
, A.[ExpRejected]
, A.[Exported]
, A.[ImpRejected]
, A.[Imported]
from
newStatistic as A
left outer join curStatistic as B
on
A.[PartitionValue] = B.[PartitionValue]
and A.[EntityID] = B.[EntityID]
where
B.[EntityID] is null
or B.[Available] <> A.[Available]
or B.[ExpRejected] <> A.[ExpRejected]
or B.[Exported] <> A.[Exported]
or B.[ImpRejected] <> A.[ImpRejected]
or B.[Imported] <> A.[Imported]
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
-- Return the current non-zero partition counts for status updates in the project DB
select
A.[MessageID]
, A.[EntityID]
, A.[PartitionValue]
from
[mig_tracking].[PartitionEventCount] as A
where
A.[InstanceCount] > 0
and A.[Time] = (
select max(B.[Time])
from [mig_tracking].[PartitionEventCount] as B
where
A.[MessageID] = B.[MessageID]
and A.[EntityID] = B.[EntityID]
and A.[PartitionValue] = B.[PartitionValue]
)
end
GO
PRINT N'Refreshing Procedure [mig_director].[Setup_ClearOther]...';
GO
EXECUTE sp_refreshsqlmodule N'[mig_director].[Setup_ClearOther]';
GO
PRINT N'Update complete.';
GO