SQL Server IMAGE data type conversion woes

Question

We're converting a legacy, SQL Server 2000 database to SQL Server 2008. Both store binary files (JPG, BMP, DOC, and PDF) in an IMAGE column (I know the datatype has been deprecated, but changing this is not an option).

The data is inserted the new system with a basic

INSERT INTO [image] Values (SELECT [image] from legacy_db);

basically a straight IMAGE to IMAGE push.

The conversion had been going well. On the new system, the JPG, BMP, and DOC file types opened without problems. The PDFs have been a nightmare.

We've found that for the PDF records, many of the rows that have been moved into SQL Server 2008 have a longer DATALENGTH() than the same rows did in the SQL Server 2000 database.

Does anyone have any clue as to why this is?

Answer 1

I'm not sure about the DATALENGTH() issue but as a workaround, if the PDF files are correct in the 2000 database (I assume you've verified this), you could try pulling those values from an app and re-saving them in the new table from the app, instead of just doing an INSERT/SELECT.

Answer 2

Resolved this issue by writing functions to locate the PDF's BOF marker and EOF marker(there can be multiple EOF markers so you have to find the last one). Stuff the bytes between the two offsets, inclusive, into a new byte array and UPDATE the BLOB field with the new byte array. After this, the PDFs open in the new system.