Read Dynamics NAV Table Metadata with SQL

Asked 19/1, 2014 at 22:55 Answered 27/9, 2023 at 11:7

Solved t-sql blob zlib dynamics-nav deflatestream

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.

Threeply answered 19/1, 2014 at 22:55 Comment(0)

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.

Threeply answered 21/1, 2014 at 15:55 Comment(7)

Wonderful! Microsoft surprises again. Why the hell add 4 stupid bytes? – Runner 22/1, 2014 at 12:39

@MakSim I assume it's a type prefix so they can store stuff in a different format if they so choose.. – Prosser 1/9, 2015 at 9:57

@VincentVancalbergh No such function in Nav though – Runner 2/9, 2015 at 8:15

@MakSim They might add one ! – Prosser 2/9, 2015 at 11:51

@VincentVancalbergh They might do it in other way, w/o creating troubles like this to developers!developers!developers! – Runner 4/9, 2015 at 8:51

Doesn't work in NAV 2009, but still works in NAV 2018 – Neutrality 29/5, 2018 at 7:24

Thank you very much. The tipp throw away the first 4 bytes saved me so much time! – Colossians 19/5, 2022 at 7:7

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.

Runner answered 20/1, 2014 at 10:8 Comment(1)

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. – Threeply 20/1, 2014 at 16:14

Apologies for absolutely raising this thread from the dead, but I finally got this working for and thought others might find it useful:

select
  CONVERT(
    varchar(max)
    , DECOMPRESS(
        CONVERT(varbinary(max), 0x1F8B0800000000000400) 
        + 
        CONVERT(varbinary(max), SUBSTRING([Metadata],5,8000))
      )
  )
from 
  [dbo].[Object Metadata] 
where 
  [Object Type] = 1 and [Object ID] = @objId

This would work out of the box if DECOMPRESS (SQL Server 2016+) had an option to ignore the gzip header but, alas, it does not. I checked a few tables and the header didn't change, so I just stuffed it on the front.

Hope this helps someone else!

Sherlocke answered 30/3, 2023 at 0:22 Comment(1)

Very interesting - thanks for the tip to use native T-SQL DECOMPRESS :-). – Threeply 30/3, 2023 at 23:22

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.

Affranchise answered 10/2, 2015 at 16:13 Comment(2)

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? – Threeply 10/2, 2015 at 16:44

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/… – Affranchise 20/2, 2015 at 16:53

Wrote this based on the input above from Brandon Roberts (thanks Brandon!) I am getting some tables that do not decompress properly and I am thinking it is possibly an issue with the header.

WITH cte AS ( select om.[Object Type], om.[Object ID], TRY_CAST(CONVERT( varchar(max) , DECOMPRESS( CONVERT(varbinary(max), 0x1F8B0800000000000400) + CONVERT(varbinary(max), SUBSTRING(om.[Metadata],5,80000)) ) ) AS XML) AS [XML], CONVERT( varchar(max) , DECOMPRESS( CONVERT(varbinary(max), 0x1F8B0800000000000400) + CONVERT(varbinary(max), SUBSTRING(om.[Metadata],5,80000)) ) ) AS [Raw] from [dbo].[Object Metadata] om where om.[Object Type] = 1 )

SELECT TOP 1000 XML.value('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; (/ns:MetaTable/@ID)[1]', 'nvarchar(100)') AS [Table No_], XML.value('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; (/ns:MetaTable/@Name)[1]', 'nvarchar(100)') AS [Table Name], FieldData.value('@ID', 'int') AS [Field No_], FieldData.value('@Name', 'nvarchar(100)') AS [Field Name], FieldData.value('@Datatype', 'nvarchar(100)') AS [Data Type], FieldData.value('@DataLength', 'int') AS [Data Length], FieldData.value('@OptionString', 'nvarchar(max)') AS [Option String], XML FROM cte CROSS APPLY XML.nodes('declare namespace ns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects"; /ns:MetaTable/ns:Fields/ns:Field[@FieldClass="Normal" and @Enabled="1"]') AS Fields(FieldData);

Festivity answered 1/8, 2023 at 22:16 Comment(0)

First of all thanks for the script!

I also ended up to have some problems with it. For me on a NAV2015 Table 27 and 5363 won't work...I can see that it is able to get meta data decompressed, but it looks like the string ends at one point leaves the XML incomplete and as such fails...

I solved this now with the following adjustment:

DECOMPRESS( CONVERT(varbinary(max), 0x1F8B0800000000000400) + CONVERT(varbinary(max), SUBSTRING(CONVERT(varbinary(max), om.[Metadata]),5,2147483647)) ) ) AS [XML],

Godric answered 27/9, 2023 at 11:7 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags