Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 309 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Converting Mycrosoft Dynamics Nav compressed BLOB to NVARCHAR with CLR
Artem
Member Posts: 2
in SQL General
One of the benefits of Ms Dynamics nav is its open physical data model. It allows us to generate external OLTP reporting, upload data for OLAP repositories with simple SQL queries without additional data extraction and decryption operations.
Everything would be fine until we come across fields with the Blob type (in SQL, this is the Image type). The length of the text field in NAV is limited to 250 characters, so MS recommends storing long lines in a field of type BLOB. An example (added in the 2017 version) is the "Work Description" field in the [Sales Header] table. Data of this type can be easily transformed into text using the expression cast (cast (imageColumn as varbinary (max)) as varchar (max)), until we have compressed = Yes attribute.
Thanks to the community, we now know that data in this format starts with a magic number 0x01447C5A (hex) To solve the problem of reading compressed BLOB data from SQL, I decided to write a CLR function, which will allow it to be used directly in database queries. SQL CLR technology has been added to MS SQL Server since version 2005. This technology allows you to extend the functionality of SQL server using code written in .NET.
Here is the link: github
After creating function you can use it like this:
Everything would be fine until we come across fields with the Blob type (in SQL, this is the Image type). The length of the text field in NAV is limited to 250 characters, so MS recommends storing long lines in a field of type BLOB. An example (added in the 2017 version) is the "Work Description" field in the [Sales Header] table. Data of this type can be easily transformed into text using the expression cast (cast (imageColumn as varbinary (max)) as varchar (max)), until we have compressed = Yes attribute.
Thanks to the community, we now know that data in this format starts with a magic number 0x01447C5A (hex) To solve the problem of reading compressed BLOB data from SQL, I decided to write a CLR function, which will allow it to be used directly in database queries. SQL CLR technology has been added to MS SQL Server since version 2005. This technology allows you to extend the functionality of SQL server using code written in .NET.
Here is the link: github
After creating function you can use it like this:
select top 10 [dbo].BlobToNVarChar([Work Description]) from [dbo].[XXX$Sales Header] where [Work Description] is not null
0