What are the possible values and text description

Question

I'm creating a report to show issues with batch jobs in Microsoft AX 2012, but have been unable to find anything to link the integer status values in the BATCHJOB table in SQL Server to the text description shown in the application. I was told this should exist in the model database in the enums, but I was unable to determine the proper links between the data in that database. I have also been unable to find this information in a web search or any of the documentation provided by Microsoft. Is anyone able to supply these values, or tell me where to find them?

SELECT bj.[STATUS] AS [bj_STATUS]
    -- 1: Didn't run - what else? 
    -- 2: Canceled - what else?
    -- 3: Error? 
    -- 4: Success?
    -- 5: ?
    -- 6: ?
    -- 7: ?
    -- 8: Withhold?
    -- Waiting
    -- Ended
    -- Withhold
    -- Executing
    -- Ready
    -- Finished
    -- Error
    -- Didn't run
    ,bj.[CAPTION] AS [bj_CAPTION]
    ,bjh.[STARTDATETIME] AS [bjh_STARTDATETIME]
    ,bjh.[ENDDATETIME] AS [bjh_ENDDATETIME]
    ,bjh.[BATCHCREATEDBY] AS [bjh_BATCHCREATEDBY]
    ,bjh.[CANCELEDBY] AS [bjh_CANCELEDBY]
    ,bg.[GROUP_] AS [bg_GROUP]
    ,bg.[DESCRIPTION] AS [bg_DESCRIPTION]
    ,bh.[SERVERID] AS [bh_SERVERID]
FROM [MicrosoftDynamicsAX].[dbo].[BATCHJOB] bj WITH(NOLOCK)
INNER JOIN [MicrosoftDynamicsAX].[dbo].[BATCHJOBHISTORY] bjh WITH(NOLOCK)
    ON bjh.[BATCHJOBID] = bj.[RECID] 
INNER JOIN [MicrosoftDynamicsAX].[dbo].[BATCH] b WITH(NOLOCK)
    ON b.[BATCHJOBID] = bj.[RECID] 
INNER JOIN [MicrosoftDynamicsAX].[dbo].[BATCHGROUP] bg WITH(NOLOCK)
    ON bg.[GROUP_] = b.[GROUPID] 
INNER JOIN [MicrosoftDynamicsAX].[dbo].[BATCHHISTORY] bh WITH(NOLOCK)
    ON bh.[BATCHID] = b.[RECID]
    AND bh.[BATCHJOBID] = bj.[RECID]
    AND bh.[BATCHJOBHISTORYID] = bjh.[RECID]
WHERE bjh.[STARTDATETIME] > GETDATE() - 1 -- AND bj.[STATUS] NOT IN(1, 2, 4)

Answer 1

I was able to figure out how to get this out of the database thanks to the information provided by @Alex Kwitny. Once I had the name of the enum, I was able to find it in the ModelElement table and link it back to the ModelElementData table on the ElementHandle value. The ModelElementData table has a Properties field stored in varbinary that can be broken down into pieces to read the values for each integer value in the main database. It took me a while to parse through everything and convert the binary into text, but now I can just reference the enum values in case anything ever changes in the future by turning my query into a function that can be called from the main query in my report.

DECLARE @name AS VARCHAR(40) = 'BatchStatus'
      , @value AS INT = 1
      , @language AS VARCHAR(8) = 'en_us';

DECLARE @bin AS VARBINARY(MAX);
SET @bin =
(
    SELECT TOP (1)
           med.[Properties]
    FROM [MicrosoftDynamicsAX_Model].[dbo].[ModelElement] me
        INNER JOIN [MicrosoftDynamicsAX_Model].[dbo].[ModelElementData] med
            ON med.[ElementHandle] = me.[ElementHandle]
    WHERE me.[Name] = @name
          AND me.[ElementType] = 40
    ORDER BY med.[LayerId] DESC
);
DECLARE @pos AS INT;
DECLARE @flags AS INT;
DECLARE @count AS INT;
DECLARE @idx AS INT;
DECLARE @off AS INT;
DECLARE @result AS VARCHAR(255);
SET @pos = 3;
SET @off = CAST(SUBSTRING(@bin, @pos, 1) AS INT) - 1;
SET @pos = @pos + 1;
WHILE @off > 0
BEGIN
    WHILE SUBSTRING(@bin, @pos, 2) <> 0x0000
    SET @pos = @pos + 2;
    SET @pos = @pos + 2;
    SET @off = @off - 1;
END;
SET @flags = CAST(SUBSTRING(@bin, @pos, 3) AS INT);
SET @pos = @pos + 3;
IF @flags & 0x008000 = 0x008000
BEGIN
    WHILE SUBSTRING(@bin, @pos, 2) <> 0x0000
    SET @pos = @pos + 2;
    SET @pos = @pos + 2;
END;
IF @flags & 0x000002 = 0x000002
    SET @pos = @pos + 1;
SET @pos = @pos + 1; 
SET @count = CAST(SUBSTRING(@bin, @pos, 1) AS INT);
IF @count > 0
BEGIN
    SET @pos = @pos + 1;
    IF @flags & 0x000200 = 0x000200
        SET @idx = @value;
    ELSE
    BEGIN
        SET @idx = 0;
        SET @off = 2 + CAST(CAST(REVERSE(SUBSTRING(@bin, @pos, 2)) AS BINARY(2)) AS INT) * 2;
        SET @off = @off + 2 + CAST(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS BINARY(2)) AS INT) * 2;
        WHILE CAST(SUBSTRING(@bin, @pos + @off + @idx, 1) AS INT) <> @value
              AND @idx < @count
        SET @idx = @idx + 1;
        IF CAST(SUBSTRING(@bin, @pos + @off + @idx, 1) AS INT) <> @value
            SET @idx = -1;
    END;
    IF @idx >= 0
    BEGIN
        SET @pos = @pos + 2;
        WHILE 1 = 1
        BEGIN
            SET @off = 0;
            SET @result = '';
            WHILE SUBSTRING(@bin, @pos + @off, 2) <> 0x0000
            BEGIN
                SET @result = @result + CHAR(CAST(REVERSE(SUBSTRING(@bin, @pos + @off, 2)) AS BINARY(2)));
                SET @off = @off + 2;
            END;
            SET @pos = @pos + @off + 2;
            IF @idx <= 0
                BREAK;
            SET @idx = @idx - 1;
        END;
    END;
    ELSE
        SET @result = '~ENUM NOT FOUND~';
END;
ELSE
    SET @result = '~ERROR~';
IF SUBSTRING(@result, 1, 1) = '@'
BEGIN
    DECLARE @module AS VARCHAR(3);
    DECLARE @label AS INT;
    SET @module = SUBSTRING(@result, 2, 3);
    SET @label = CAST(SUBSTRING(@result, 5, DATALENGTH(@result) - 4) AS INT);
    SET @result =
    (
        SELECT TOP (1)
               [Text]
        FROM [MicrosoftDynamicsAX_Model].[dbo].[ModelElementLabel]
        WHERE [LabelId] = @label
              AND [Module] = @module
              AND [Language] = @language
        ORDER BY [LayerId] DESC
    );
END;

SELECT @value AS [Id]
     , @result AS [Status];

Answer 2

The enum is BatchStatus, which is stored in the modelstore DB, but I don't think the enum names and/or values are stored. It represents an integer ultimately.

Below is the base enum with the integer values 0-8:

The text labels are the same as the name (in English that is) except for the below exceptions.

• 0 - Withhold (Hold)
• 4 - Ended (Finished)
• 6 - Didn't run (NotRun)

You could probably just create a SQL stored procedure with a CASE statement as these statuses are rarely changed from base code.