Søren Eggert Lundsteen Olsen
8db226d2b6
Cascorp's BATCH table: every STATUS 4 row has an end time and no exception code (finished), STATUS 1 rows have no end time and starts long passed (waiting). So AX 3.0 uses the same order as AX 4.0+: 0 Tilbageholdt, 1 Venter, 2 Udfører, 3 Fejl, 4 Afsluttet. The old assumption (0 Venter ... 4 Tilbageholdt) treated finished jobs as held and waiting ones as running. SQLI-AX-003 skips waiting jobs planned more than BatchAbandonedDays (30) ago: the customer has 25 such rows from 2007-2018 that no batch server serves; they are dead rows, not a live alarm. Adds docs/dynamics-ax/verificer-ax30-batchstatus.sql (read-only) to repeat the check at other AX 3.0 customers. Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
53 lines
3.2 KiB
SQL
53 lines
3.2 KiB
SQL
-- ===================================================================================================================
|
|
-- Verificér STATUS-værdierne i BATCH for Axapta 3.0
|
|
-- ===================================================================================================================
|
|
-- Formål: SQLInsight bruger for AX 3.0: 0 = Tilbageholdt, 1 = Venter, 2 = Udfører, 3 = Fejl, 4 = Afsluttet
|
|
-- (1 og 4 kontrolleret hos Cascorp 2026-10-03).
|
|
-- Tekst-labels for enums ligger ikke i databasen, så scriptet viser værdierne sammen med de
|
|
-- kolonner, der afslører betydningen (sluttid, fejlkode, planlagt start), og eksempler, som kan
|
|
-- slås op i AX-klienten (Basis > Forespørgsler > Batchliste).
|
|
-- Read-only: Kun SELECT. Ingen temp-tabeller, intet skrives. Kort lock timeout, så batchkørsler ikke blokeres.
|
|
-- Kør: I SSMS i AX-databasen (fx AXDBTEST på KGA-SQL-01\AX30). Skemaet er bmssa hos Cascorp;
|
|
-- hedder det dbo hos jer, så erstat "bmssa." med "dbo.".
|
|
-- ===================================================================================================================
|
|
|
|
SET NOCOUNT ON;
|
|
SET LOCK_TIMEOUT 2000;
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
|
|
-- 1. Fordeling: hvor mange job har hver STATUS, og hvad kendetegner dem?
|
|
SELECT
|
|
b.STATUS,
|
|
COUNT(*) AS antal,
|
|
SUM(CASE WHEN b.ENDDATE > '19000101' THEN 1 ELSE 0 END) AS med_sluttid,
|
|
SUM(CASE WHEN b.EXCEPTIONCODE <> 0 THEN 1 ELSE 0 END) AS med_fejlkode,
|
|
SUM(CASE WHEN DATEADD(second, b.STARTTIME, b.STARTDATE) > GETDATE()
|
|
THEN 1 ELSE 0 END) AS start_i_fremtiden,
|
|
SUM(CASE WHEN b.SESSIONIDX <> 0 THEN 1 ELSE 0 END) AS med_session,
|
|
MIN(DATEADD(second, b.STARTTIME, b.STARTDATE)) AS tidligste_start,
|
|
MAX(DATEADD(second, b.STARTTIME, b.STARTDATE)) AS seneste_start
|
|
FROM bmssa.BATCH AS b
|
|
GROUP BY b.STATUS
|
|
ORDER BY b.STATUS;
|
|
|
|
-- 2. Op til 5 eksempler pr. STATUS (nyeste først) til at slå op i AX-klientens batchliste.
|
|
SELECT x.STATUS, x.COMPANY, x.GROUPID, x.CLASSNUM, x.JOURNALID, x.EXECUTEDBY,
|
|
x.planlagt_start, x.slut, x.EXCEPTIONCODE, x.SESSIONIDX, x.RECID
|
|
FROM (
|
|
SELECT b.STATUS, b.COMPANY, b.GROUPID, b.CLASSNUM, b.JOURNALID, b.EXECUTEDBY,
|
|
DATEADD(second, b.STARTTIME, b.STARTDATE) AS planlagt_start,
|
|
CASE WHEN b.ENDDATE > '19000101'
|
|
THEN DATEADD(second, b.ENDTIME, b.ENDDATE) END AS slut,
|
|
b.EXCEPTIONCODE, b.SESSIONIDX, b.RECID,
|
|
ROW_NUMBER() OVER (PARTITION BY b.STATUS ORDER BY b.STARTDATE DESC, b.STARTTIME DESC) AS rn
|
|
FROM bmssa.BATCH AS b
|
|
) AS x
|
|
WHERE x.rn <= 5
|
|
ORDER BY x.STATUS, x.planlagt_start DESC;
|
|
|
|
-- 3. Hvilken feltype har STATUS ifølge AX' egen dictionary? (FIELDTYPE 4 = enum)
|
|
SELECT d.TABLEID, d.FIELDID, d.NAME, d.SQLNAME, d.FIELDTYPE
|
|
FROM bmssa.SQLDICTIONARY AS d
|
|
WHERE d.SQLNAME = 'STATUS'
|
|
AND d.TABLEID = (SELECT TOP (1) t.TABLEID FROM bmssa.SQLDICTIONARY AS t
|
|
WHERE t.FIELDID = 0 AND t.SQLNAME = 'BATCH');
|