Almost 15 years ago AndyD from AxForum blew me away with a post that was giving a glimpse into how the X++ containers are stored in the database.
Ever since then I had a TODO for myself to write a blog post explaining this in detail and covering the various types that can be stored, basically allowing to review the contents of the container from DB without writing X++ code.
Luckily, today is the day! As they say,
A colleague of mine, Maciej Plaza, has written a stored procedure, that shows the contents of a container stored in a database field.
Some inspiration was taken from the following blog post too: http://abraaxapta.blogspot.com/2011/06/accessing-dynamics-ax-containers-from.html
Here's an example of how you could use this to see the batch task's infolog message contents:
declare @bin as varbinary(max);
select top(1) @bin = info from batch where recid = <recid>
exec ##PRINT_CONTAINER @binHere is how the output looks like:
 |
| Browsing the X++ container contents in SQL |
Here is the stored proc (Also available as a file in OneDrive):
CREATE PROCEDURE ##PRINT_CONTAINER @container VARBINARY(MAX)
AS
BEGIN
DECLARE @pos AS int;
SET @pos = 1;
DECLARE @offset AS int;
DECLARE @indent as int;
set @indent = 0;
declare @result as varchar(max);
IF SUBSTRING(@container, @pos, 2) = 0x07FD
BEGIN
set @result = '<container>' + char(13);
set @indent = @indent + 2;
SET @pos = @pos + 2;
WHILE @indent > 0
BEGIN
IF SUBSTRING(@container, @pos, 1) = 0x00 --STRING
BEGIN
set @result = @result + replicate(' ', @indent) + '<string>'
SET @pos = @pos + 1;
SET @offset = 0;
declare @string as varchar(max);
set @string = '';
WHILE SUBSTRING(@container, @pos + @offset, 2) <> 0x0000
BEGIN
SET @string = @string +
CHAR(CAST(REVERSE(SUBSTRING(@container, @pos + @offset, 2)) AS binary(2)))
SET @offset = @offset + 2;
END
SET @pos = @pos + @offset + 2;
set @result = @result + @string + '</string>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x01 --INT
BEGIN
set @result = @result + replicate(' ', @indent) + '<int>'
SET @pos = @pos + 1;
declare @int as int;
SET @int = CAST(CAST(REVERSE(SUBSTRING(@container, @pos, 4)) AS binary(4)) as int);
SET @pos = @pos + 4;
set @result = @result + cast(@int as varchar(max)) + '</int>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x02 --REAL
BEGIN
set @result = @result + replicate(' ', @indent) + '<real>'
SET @pos = @pos + 1;
DECLARE @temp as binary(8);
SET @temp = CAST(REVERSE(SUBSTRING(@container, @pos + 2, 8)) AS binary(8));
DECLARE @val bigint;
SET @val = 0;
DECLARE @dec AS int;
SET @offset = 1;
WHILE (@offset <= 8)
BEGIN
SET @val = (@val * 100) +
(CAST(SUBSTRING(@temp, @offset, 1) AS int) / 0x10 * 10) +
(CAST(SUBSTRING(@temp, @offset, 1) AS int) % 0x10);
SET @offset = @offset + 1;
END
WHILE @val <> 0 AND @val % 10 = 0
SET @val = @val / 10;
SET @dec = (CAST(SUBSTRING(@container, @pos, 1) AS int) + 0x80) % 0x100;
declare @real as real;
SET @real = CAST(@val AS real);
WHILE @dec >= LEN(CAST(@val AS varchar)) + 0x80
BEGIN
SET @real = CAST(@real AS real) * 10.0;
SET @dec = @dec - 1;
END
SET @dec = (CAST(SUBSTRING(@container, @pos, 1) AS int) + 0x80) % 0x100 + 1;
WHILE @dec < LEN(CAST(@val AS varchar)) + 0x80
BEGIN
SET @real = CAST(@real AS real) / 10.0;
SET @dec = @dec + 1;
END
IF SUBSTRING(@container, @pos + 1, 1) = 0x80
SET @real = 0 - CAST(@real AS real);
SET @pos = @pos + 10;
set @result = @result + @real + '</real>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x03 --DATE
BEGIN
set @result = @result + replicate(' ', @indent) + '<date>'
SET @pos = @pos + 1;
DECLARE @year char(4);
DECLARE @month char(2);
DECLARE @day char(2);
SET @year = SUBSTRING(@container, @pos, 1) + 1900;
SET @month = SUBSTRING(@container, @pos + 1, 1) + 1;
SET @day = SUBSTRING(@container, @pos + 2, 1) + 1;
IF LEN(@month) < 2
SET @month = '0' + @month;
IF LEN(@day) < 2
SET @day = '0' + @day;
declare @date as date;
SET @date = CAST(@year + '-' + @month + '-' + @day AS date);
SET @pos = @pos + 3;
set @result = @result + convert(varchar(max), @date) + '</date>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x04 --ENUM
BEGIN
set @result = @result + replicate(' ', @indent) + '<enum>'
SET @pos = @pos + 1;
declare @enum as int;
SET @enum = CAST(SUBSTRING(@container, @pos, 1) AS int);
SET @pos = @pos + 3;
set @result = @result + @enum + '</enum>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x06 --DATETIME
BEGIN
set @result = @result + replicate(' ', @indent) + '<datetime>'
SET @pos = @pos + 1;
DECLARE @year2 char(4);
DECLARE @month2 char(2);
DECLARE @day2 char(2);
DECLARE @hour char(2);
DECLARE @min char(2);
DECLARE @sec char(2);
SET @year2 = SUBSTRING(@container, @pos, 1) + 1900;
SET @month2 = SUBSTRING(@container, @pos + 1, 1) + 1;
SET @day2 = SUBSTRING(@container, @pos + 2, 1) + 1;
SET @hour = SUBSTRING(@container, @pos + 3, 1) + 0;
SET @min = SUBSTRING(@container, @pos + 4, 1) + 0;
SET @sec = SUBSTRING(@container, @pos + 5, 1) + 0;
IF LEN(@month2) < 2
SET @month2 = '0' + @month2;
IF LEN(@day2) < 2
SET @day2 = '0' + @day2;
IF LEN(@hour) < 2
SET @hour = '0' + @hour;
IF LEN(@min) < 2
SET @min = '0' + @min;
IF LEN(@sec) < 2
SET @sec = '0' + @sec;
declare @datetime as datetime;
SET @datetime = CAST(@year2 + '-' + @month2 + '-' + @day2 + ' ' + @hour + ':' + @min + ':' + @sec AS datetime);
SET @pos = @pos + 12;
set @result = @result + @datetime + '</datetime>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x07 --CONTAINER
BEGIN
set @result = @result + replicate(' ', @indent) + '<container>' + char(13);
SET @pos = @pos + 3;
set @indent = @indent + 2;
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0xFF --CONTAINER END
BEGIN
SET @pos = @pos + 1;
set @indent = @indent - 2;
set @result = @result + replicate(' ', @indent) + '</container>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x2D --GUID
BEGIN
SET @pos = @pos + 1;
SET @offset = 0;
declare @guid as uniqueidentifier
SET @guid = CAST(CAST(
REVERSE(SUBSTRING(@container, @pos, 4)) +
REVERSE(SUBSTRING(@container, @pos + 4, 4)) +
REVERSE(SUBSTRING(@container, @pos + 8, 4)) +
REVERSE(SUBSTRING(@container, @pos + 12, 4)
) AS binary(16))AS uniqueidentifier);
SET @pos = @pos + 16;
set @result = @result + replicate(' ', @indent) + '<guid>' + cast(@guid as varchar(80)) + '</guid>' + char(13)
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x30 --BLOB
BEGIN
SET @pos = @pos + 1;
SET @offset = CAST(CAST(REVERSE(SUBSTRING(@container, @pos, 4)) AS binary(4)) AS int);
SET @pos = @pos + 4;
declare @blob as varbinary(max);
SET @blob = CAST(SUBSTRING(@container, @pos, @offset) AS varbinary(max));
SET @pos = @pos + @offset;
set @result = @result + replicate(' ', @indent) + '<blob>' + CONVERT(VARCHAR(max), @blob, 2) + '</blob>' + char(13);
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0x31 --INT64
BEGIN
SET @pos = @pos + 1;
declare @bigint as bigint;
SET @bigint = CAST(CAST(REVERSE(SUBSTRING(@container, @pos, 8)) AS binary(8)) AS bigint);
SET @pos = @pos + 8;
set @result = @result + replicate(' ', @indent) + '<int64>' + cast(@bigint as varchar(max)) + '</int64>' + char(13)
END
ELSE IF SUBSTRING(@container, @pos, 1) = 0xFC --ENUMLABEL
BEGIN
SET @pos = @pos + 1;
DECLARE @value int;
DECLARE @name varchar(40);
SET @value = SUBSTRING(@container, @pos, 1);
SET @pos = @pos + 1;
SET @offset = 0;
SET @name = '';
WHILE SUBSTRING(@container, @pos + @offset, 2) <> 0x0000
BEGIN
SET @name = CAST(@name AS varchar(40)) +
CHAR(CAST(REVERSE(SUBSTRING(@container, @pos + @offset, 2)) AS binary(2)))
SET @offset = @offset + 2;
END
declare @enumlabel as varchar(max);
SET @enumlabel = CAST(@name + ':' + CAST(@value as varchar(3)) as varchar(44));
SET @pos = @pos + @offset + 2;
set @result = @result + replicate(' ', @indent) + '<enumlabel>' + @enumlabel + '</enumlabel>' + char(13)
END
ELSE
begin
declare @errormsg varchar(max);
set @errormsg = 'Unexpected type ' + CONVERT(VARCHAR(1000), SUBSTRING(@container, @pos, 1), 2);
throw 50000, @errormsg, 1;
end
END
END
print @result
ENDHope this helps!
CREATE PROCEDURE ##PRINT_CONTAINER throws the following error:
ReplyDeleteImplicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in add operator.
Any ideas?