Extracting Embedded Binary Data (Attachments) from a SQL Server 2005 Table to a File Server

• n0012

  SSC Veteran

  Points: 226

  More actions

  October 3, 2008 at 10:30 am

  #382939

  I am trying to extract embedded attachment data from a SQL Server 2005 image data field. I have found one method that nearly works, which uses the BCP utility.

  SET @filepath = 'N:\Attachments\document.pdf'

  SET @cmd = 'bcp "select binaryImageField from server.database.schema.attachmentTable where id=100" queryout "'+@filepath+'" -T -n';

  EXEC master..xp_cmdshell @cmd;

  Putting this sql code inside a while loop enables me to extract all attachments into a file server directory. The only problem I am facing is that PDFs are the only attachments that actually open. JPG, DOC, XLS etc do not - they appear corrupted.

  Can anybody shed some light on why one file format works here? Or is there a better utility to use to accomplish the same task?

• n0012

  SSC Veteran

  Points: 226

  More actions

  October 7, 2008 at 9:59 am

  #881779

  Any thoughts on this? Should I post the same question to other sections of the forum?