tsql - Read Dynamics NAV Table Metadata with SQL

Question

I would like to be able to read the Dynamics NAV 2013 Table Metadata directly from the SQL Server database without requiring the NAV Development Environment.

I can view the binary SQL "image" BLOB columns with a query like the following (filter as appropriate with WHERE clause):

SELECT 
    o.[Name],
    m.[Object Type],
    m.[Metadata], -- XML Metadata
    m.[User Code], -- C# Metadata
    m.[User AL Code] -- C/AL Metadata
FROM [Navision].[dbo].[Object Metadata] AS m
JOIN [Navision].[dbo].[Object] AS o
ON m.[Object ID] = o.[ID]
AND o.[Company Name] = 'YourCompanyName'
AND o.[Type] = 0 -- 0 is NAV Table Object Type

I can save the binary data from the [Metadata], [User Code], and [User AL Code] off to files using .Net code or a quick script with SQL driver. I have tried using 7-zip to decompress, a hex editor to view, and the Cygwin "file" command to detect these BLOB file types.

Unfortunately I can't figure out how to decode or decompress the binary data into a readable or usable format. Until I can use the data in those fields directly, I must open NAV Dev Environment and use the Object Designer to view the comma-separated OptionString properties for zero-based lookup list drop-down menu (each item in the list is stored in the back-end database as an integer - 0 for first item, 1 for second, etc). The string values are not present in a SQL lookup table, but NAV does put them in the table metadata blobs.

This is the missing link for me to completely support my NAV users as a DBA without needing a NAV developer to lookup these number-to-name NAV custom field mappings for me. I can then lookup these list values and create matching SQL CASE statements or custom lookup tables as needed.

Once I have this piece I should be able to create advanced SQL views, queries, reports, and tools with no need for access to the Dynamics NAV front-end user or developer tools.

Please let me know if you have knowledge of the binary data format used for these NAV Object Metadata blob properties. Any advice on how to convert to a readable or usable format would be helpful.

Answer 1

I was able to get an answer for the format of these Metadata binary fields from the author of the deV.ch - man vs. code, Dynamics NAV & C# .NET blog. Based on the reverse engineering by devch, we determined that the first four bytes (32 bits) of these fields are used by NAV to store a "magic number" that determines the custom NAV Blob type.

In the case of these metadata fields, the NAV Compressed Blob-Type magic number is 0x02457d5b (hex). In order to use the standard .Net DeflateStream to Decompress, just throw away those first four magic-number bytes and then process the rest of the stream with DeflateStream as usual.

I was able to successfully test this process with .Net, now I plan to test with Python or some other non-Microsoft deflate tools to see if the deflate implementation follows the industry standard. Thanks again to devch for the article that led to this solution: Accessing Compressed Blobs from outside NAV (NAV2013) (Revisited).

Update: tested with Python zlib and it works! Standards-compliant Deflate algorithm is used once the custom NAV Blob-type magic number is removed. Here's some sample code (Python):

# Example Using Python 3.x
import zlib, sys, struct

# NAV custom Blob-Type identifier (first 4 bytes)
magic = struct.unpack('>I',sys.stdin.buffer.read(4))[0]
print('magic number = %#010x' % magic, file=sys.stderr)
# Remaining binary data is standard DEFLATE without header
input = sys.stdin.buffer.read()
output = zlib.decompress(input,-15)
sys.stdout.buffer.write(output)

Use something like the following to test:

python -u test.py < Input_Meta.blob > Output_Meta.txt

Of course the .Net DeflateStream works after removing the first four bytes as well. This example is just to show that you're not limited to using .Net languages.