Using T-SQLs to help with the Management Reporter

The case study is Management Reporter CU14, working together with AX 2012 R3. I was trying to understand how the jobs are being picked up and executed by the process service, since I am running into some issues from times to times with the synchronization process.

Following is a summary of the T-Sql statements I used and am being successful in restarting the jobs to execute in case they stop, as well as better viewing what is logged in the system. You can find the explanation of what each does directly in text.

I won't be stating the obvious, you should use these if your instance is broken and you don't want to reinstall again the Data Mart.

-- VIEW ONLY - TASKS / REGISTERS / TRIGGERS

-- display the list of the task present in the system and their state
select * from Scheduling.TaskState ts
inner join Scheduling.Task t on t.Id = ts.TaskId

-- the schedulers registered in the system
select * from Scheduling.SchedulerRegister
    order by LastRunTime desc

-- view the trigger settings for the task
select * from Scheduling.[Trigger] tr
inner join Scheduling.Task t on t.TriggerId = tr.Id

-- VIEW ONLY - LOGS


-- view the list of logged messages
select * from Scheduling.Log l
inner join Scheduling.Task t on t.Id = l.TaskId
    order by l.StartTime desc

-- view the list of messages
select * from Scheduling.Message

-- view easy output summary
select * from Scheduling.MessageSummary

-- view the list of logged errors
select * from Scheduling.TaskError

-- UPDATE HERE

-- run these statements with the Services STOPPED


-- update the time for update companies from 300 to 45 seconds, this can apply to other jobs depending on your needs
/*
update Scheduling.[Trigger]
set Interval = 45
where UnitOfMeasure = 1 and
    Interval = 300
*/


-- run these statements with the Services STOPPED

-- update the task state status to unprocessed (0) or completed (5), 3 is running, 7 is failed; 
-- also update the:
-- next running time (be very careful with the TimeZone - it is not applying the TZ so you have to do that),
-- the scheduler (it must be null to be picked up by the latest one spawned from the restart of the process service)
/*
update Scheduling.TaskState
set StateType = 0, -- I also used 5 and it seems to be ok,
    NextRunTime = DATEADD(mi, -119, GETDATE()), -- ss for seconds, run this in the next minute, here I used a GMT+2 hour time,
    SchedulerRegisterId = null
*/