title: Exporting binary file data (images) from SQL via a stored procedure
body: <p>I am trying to export a fairly large number of image files, stored internally in an SQL database as binary data. </p>
  
  <p>Being fairly new to writing stored procedures in SQL, I have come across a couple of very useful guides on how this can be archived, but I seem to be missing something.</p>
  
  <p>I am running SQL Server 2008 R2 locally, and I am trying to write the files to a folder on my C:\ drive.</p>
  
  <p>Here is the buisness part of what I have so far:</p>
  
  <pre><code>BEGIN
  DECLARE @cmd VARCHAR(8000)
  DECLARE @result int
  
  DECLARE curExportBinaryDocs CURSOR FAST_FORWARD FOR
  SELECT 'BCP "SELECT Photograph_Data FROM [ALBSCH Trial].[dbo].[Photograph] WHERE Photograph_ID = '
    + CAST(Photograph_ID AS VARCHAR(500)) + '" queryout "' + @OutputFilePath 
    + CAST(Photograph_ID AS VARCHAR(500)) + '.jpg"' + ' -n -T'
  FROM dbo.Photograph
  
  OPEN curExportBinaryDocs   
  FETCH NEXT FROM curExportBinaryDocs INTO @cmd
  WHILE @@FETCH_STATUS = 0
    BEGIN
       --PRINT @cmd
       EXEC @result = xp_cmdshell @cmd         
       FETCH NEXT FROM curExportBinaryDocs INTO @cmd
    END 
  CLOSE curExportBinaryDocs
  DEALLOCATE curExportBinaryDocs
  END
  </code></pre>
  
  <p>'@result' is always being set to '1' (failed) after the xp_cmdshell call. All the table names/fields are correct, so I suspect there is something wrong with my BCP call, but I am not sure what to try next.</p>
  
  <p>Any help or advice would be very welcome.</p>