BLOB transfer from NAV v2.6 to NAV v.90 via SQL Query

tarkmyler

I have an ancient version 6.1 database that has a table that contains a BLOB field. The BLOB field is used to store a PDF file. I'd love to use a SQL Query as shown below.

INSERT INTO [Nav90Database].[PDF_Storage_Table] ([TableKeyField], [BlobField])
SELECT [TableKeyField], [BlobField] FROM [NAV61Database].[PDF_Storage_Table].

However when I transfer the data using the SQL query and issue a CALCFIELDS in NAV 2016, I get the message "The PDF_Storage_Table contains a BLOB field with compression data that is not valid".

It seems that I have to do some sort of SQL .net voodoo to get these PDF files across the great divide. I've searched the forum without any luck. Any suggestions?

Slawek_Guzek

The problem here is that before NAV 2013 version NAV used a bespoke compression internally, whatever you have in your BLOBS it is stored using that internal compression algorithm.

NAV2013 onward also uses internally compression but it uses some standard .NET compression method.

In order to be able to transfer the data you between both databases, you need to turn off BLOB field compression in both tables.

You need to edit the table in the source database, then in the BLOB field property turn off compression. Bear in mind that when you save the table it will take a long time and your database will grow, potentially significantly, as NAV during that operation will rewrite the content of the table, storing uncompressed data. It will also blow up your database transaction log file- if you have lots of data.

Then you need to do the same in your destination database. Since the table is empty it will not take long. After this, you should be able to transfer the data using TSQL Query.

Once you transfer all the data you may want to edit the table again and go back to the default compression setting on your BLOB field - but again this will take a long time, and will blow up your transaction log file in the destination database.


You would be probably better writing a simple C/AL code exporting all binary data from the old database into a set of temporary files (NAV will decompress the BLOB data for you if you stream it out to a file), and then another bit of C/AL to import files back into new NAV. It might be quicker than uncompress + move uncompressed + compress, and it allows you to control transaction log size by committing imported BLOBS every so often

tarkmyler

Wow. It worked like a champ, and didn't take long either. NAV 2016 was a bit cranky about uncompressing with data in the table, but that was easily handled. You saved me a lot of time. Thanks a lot!