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.

Solution 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.

Solution 2

It is possible to extract metadata from this table through Nav and as I can see it is stored as plain text but in binary field. It can be saved to file using simple MemoryStream (in Nav it is called OutSteram). So for table number 3 I get following XML:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<MetaTable xmlns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects" ID="3" CaptionML="ENU=Payment Terms;RUS=Условия платежа" DataPerCompany="1" Name="Payment Terms" LookupFormID="4" DataCaptionFields="1,5">
    <Fields>
        <Field ID="1" Datatype="Code" DataLength="10" Enabled="1" FieldClass="Normal" Name="Code" CaptionML="ENU=Code;RUS=Код" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="1" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="2" Datatype="DateFormula" Enabled="1" FieldClass="Normal" Name="Due Date Calculation" CaptionML="ENU=Due Date Calculation;RUS=Расчет срока оплаты" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="3" Datatype="DateFormula" Enabled="1" FieldClass="Normal" Name="Discount Date Calculation" CaptionML="ENU=Discount Date Calculation;RUS=Расчет даты скидки" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="4" Datatype="Decimal" Enabled="1" FieldClass="Normal" Name="Discount %" CaptionML="ENU=Discount %;RUS=Скидка (%)" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" MinValue="0" MaxValue="100" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="5" Datatype="Text" DataLength="50" Enabled="1" FieldClass="Normal" Name="Description" CaptionML="ENU=Description;RUS=Описание" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
        <Field ID="6" Datatype="Boolean" Enabled="1" FieldClass="Normal" Name="Calc. Pmt. Disc. on Cr. Memos" CaptionML="ENU=Calc. Pmt. Disc. on Cr. Memos;RUS=Расчет скидки оплаты по кредит-нотам" BlankNumbers="DontBlank" BlankZero="0" SignDisplacement="0" Editable="1" NotBlank="0" Numeric="0" DateFormula="0" ClosingDates="0" Title="0" AutoIncrement="0" ValidateTableRelation="1" TestTableRelation="1" ExtendedDatatype="None"/>
    </Fields>
    <Keys>
        <Key Enabled="1" Key="Field1" MaintainSQLIndex="1" MaintainSIFTIndex="1" Clustered="1"/>
    </Keys>
    <FieldGroups>
        <FieldGroup GroupID="1" GroupName="DropDown" GroupFields="Field1,Field5,Field2"/>
    </FieldGroups>
</MetaTable>

Suppose this is what you want.

Code writing it to file in Nav will look like this:

ObjectMetadata:Record(Object Metadata)
Code:BigText
File:File       
CodeStream:InStream     
FileStream:OutStream        

ObjectMetadata.INIT;

IF ObjectMetadata.GET(1,3) THEN
 BEGIN
  ObjectMetadata.CALCFIELDS(Metadata);
  File.CREATE('C:\temp\Code.txt');
  File.CREATEOUTSTREAM(FileStream);

  clear(codestream);
  ObjectMetadata."Metadata".CREATEINSTREAM(CodeStream);
  Code.READ(CodeStream);
  Code.WRITE(FileStream);

  file.close();
 END;

Now you have options: try to do same thing in SQL/.Net (I'm not keen with it) or you can ask your Nav developer to make some kind of batch job that will periodically (or on demand) process all tables' matadata and save it to external table/file/whatever which you can access from SQL.

Solution 3

Why not use webservices and get the data you need without having to do anything fancy?

Users can then use PowerPivot for Excel and create their own reports.

You could also use the Excel Add-In for NAV and just export data to excel directly from NAV and then refresh from within Excel.

Comments

[Mister_Tom]

Thanks for the answer Mak, while I would prefer the ability to access the NAV Metadata directly from SQL/.Net it is helpful to have a code sample that my NAV developers can use to provide this metadata for me.

[Mak Sim]

Wonderful! Microsoft surprises again. Why the hell add 4 stupid bytes?

[Mister_Tom]

Creating a web service sounds very fancy. Is there a web service built into NAV that allows easy access to data? Please provide a simple example of how to leverage this web service capability for easy data access by NAV users. Do you have a link or example for the Excel Add-In for NAV? I have not heard of this - is it a Microsoft provided component?

[Steve]

Web Services are built in to NAV. If you type Web Services into the Search Bar on the Role Center, you can then publish a Page, Codeunit or Query and make it available via SOAP or ODATA. saurav-nav.blogspot.co.uk/2013/11/…

[Vincent Vancalbergh]

@MakSim I assume it's a type prefix so they can store stuff in a different format if they so choose..

[Mak Sim]

@VincentVancalbergh No such function in Nav though

[Vincent Vancalbergh]

@MakSim They might add one !

[Mak Sim]

@VincentVancalbergh They might do it in other way, w/o creating troubles like this to developers!developers!developers!

[SeeR]

Doesn't work in NAV 2009, but still works in NAV 2018

[NemesisMF]

Thank you very much. The tipp throw away the first 4 bytes saved me so much time!