Accessing Dynamics AX Containers from SQL

If anyone has dealt with storing container objects in the database, you also know of how impossible it is to access it's contents from outside of DAX.  I spent a couple of days reverse engineering the binary format of containers and how it stores information.  From that, I have developed a couple of SQL functions that allow you to dig into these containers from wherever you need to (SSRS, Management Studio, etc.).

There are two functions:

• CONPEEK(varbinary, int)
• CONSIZE(image)

CONPEEK acts exactly as DAX handles it so it should be no mystery.  CONSIZE is used within the CONPEEK function to handle nested containers, it will just give you the size in bytes of the container (Not to be confused with conlen() in DAX).

Note: Sorry, you must CAST the return value of CONPEEK as a varbinary(8000) before calling back into CONPEEK when dealing with nested containers. I could not find a way around it.

Usage:

DECLARE @con AS varbinary(8000);
-- An example container with structure:
-- * 2 (int)
-- * (container)
--    * 17 (int)
--    * 'abc' (str)
SET @con = 0x07FD01020000000707FD0111000000006100620063000000FFFF;

SELECT CONPEEK(@con, 1); --returns 2
SELECT CONPEEK(@con, 2); --returns a container (0x07FD0111000000006100620063000000FF)

SELECT CONPEEK(CAST(CONPEEK(@con, 2) AS varbinary(8000)), 1); --returns 17
SELECT CONPEEK(CAST(CONPEEK(@con, 2) AS varbinary(8000)), 2); --returns 'abc'

Downloads:

• ConPeek.sql
• ConSize.sql

Changelog:

• 2011-11-21 - Bug - Strings were limiting to 30 characters, this is fixed
• 2012-11-15 - Feature - Added ability to read utcDateTime elements
• 2013-11-27 - Feature - Added ability to read EnumLabel elements
• 2014-04-02 - Bug - Fixed DateTime bug...Workaround added for unknown 0x31 type
• 2014-06-07 - Bug - Fixed Enum bug, int to string conversion error
• 2014-06-09 - Bug - Fixed DateTime bug, wrong parameter was referenced
• 2014-08-27 - Feature - Added ability to read Int64 elements
• 2014-12-10 - Bug - Fixed DateTime bug, conversion issue from string to datetime is fixed
• 2015-12-10 - Feature - Added ability to read BLOB elements
• 2015-12-10 - Bug - Fixed Enum bug, returns enum int value instead of 0 or 1