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