axtoday: Management Reporter scripts

[Blog bot]

Источник: http://axtoday.blogspot.com/2017/11/...r-scripts.html
==============

Few scripts that can be used to check the MR integration tasks:

Check all integration tasks that have been executed:

--List details about each DDM task: state, progress, last...
--List details about each DDM task: state, progress, last/next runtime in local time, and the interval that each runs
--Status 5=Success; 3=Running; 6=Cancelled
select CIG.[Description]
, STK.[Name]
, STS.[Progress]
, CASE STS.[StateType]
WHEN 3 THEN 'Processing'
WHEN 5 THEN 'Complete'
WHEN 7 THEN 'Error'
END AS StateType
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), STS.[LastRunTime]) as LocalLastRunTime
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), STS.[NextRunTime]) as LocalNextRunTime
, CM.[ContinueOnRecordError]
, STRG.[Interval]
, CASE STRG.[UnitOfMeasure]
WHEN 2 THEN 'Minutes'
ELSE 'Seconds'
END AS IntervalTiming
, CASE STRG.[IsEnabled]
WHEN 1 THEN 'Enabled'
ELSE 'Disabled'
END AS NameStatus
from [Connector].[Map] CM with (nolock)
inner join [Scheduling].[Task] STK with (nolock) on STK.[Id] = CM.[MapId]
inner join [Scheduling].[TaskState] STS with (nolock) on STK.[Id] = STS.[TaskId]
inner join [Connector].[IntegrationGroup] CIG with (nolock) on CIG.[IntegrationId] = STK.[CategoryId]
inner join [Scheduling].[Trigger] STRG with (nolock) on STK.[TriggerId] = STRG.[Id]
order by CIG.[Description], STK.[Name]





Check the tasks outcomes

select CIG.[Description], ST.[Name], SM.[Text],
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[StartTime]) as LocalStartTime,
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[EndTime]) as LocalEndTime,
SL.[TotalRetryNumber], SL.[IsFailed], STT.[Name] as TaskType
from [Scheduling].[Log] SL with (nolock)
inner join [Scheduling].[Task] ST with (nolock) on SL.TaskId = ST.Id
inner join [Scheduling].[Message] SM with (nolock) on SL.Id = SM.LogId
inner join [Scheduling].[TaskType] STT with (nolock) on ST.TypeId = STT.Id
inner join [Connector].[IntegrationGroup] CIG with (nolock) on CIG.[IntegrationId] = ST.[CategoryId]
order by SL.[StartTime] desc





Check the current integration activities

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext





Источник: http://axtoday.blogspot.com/2017/11/...r-scripts.html