Today I was asked if there was a way to read an image file from the Dynamics GP Document Attach table so that the image can be added to a report. For instance, suppose a customer wants to display item images on an invoice.
I have done very little work with the blob / varbinary field type in SQL Server, so I didn't know how difficult it would be to do this. I quickly did some research and testing, and while I don't have a complete solution for inserting images onto a report, I did test one method for extracting files from the Dynamics GP Document Attach table and saving them to disk.
From what I could tell, there are at least three standard SQL Server tools/commands that you can use to extract an image or file from a varbinary field.
1. OLE Automation Query – This looks pretty sketchy and appears to be very poorly documented. I tried some samples and couldn’t get it to work.
2. CLR (.NET) inside of SQL – Appears to be a viable option, but requires enabling CLR on SQL, which I personally would try to avoid on a GP SQL Server if possible, so I didn't try this one yet
3. BCP – I was able to get this to work and it was surprisingly easy, but I don’t know how easy it will be to integrate into a report process