Exporting binary file data (images) from SQL via a stored procedure

Question

I am trying to export a fairly large number of image files, stored internally in an SQL database as binary data.

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.

I am running SQL Server 2008 R2 locally, and I am trying to write the files to a folder on my C:\ drive.

Here is the buisness part of what I have so far:

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

'@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.

Any help or advice would be very welcome.

Answer 1

Well, first of all.. (and sorry about that ;) ) DON"T USE CURSORS.. and sorry for the caps...

One of the most baddest things about cursors are that they can lock your table. What i always do for these purposes (and which is quite faster), i use a for loop.. like this

declare @totrow int
      , @currow int
      , @result int
      , @nsql nvarchar(max)

declare @sqlStatements table (
  Id int identity(1, 1)
, SqlStatement varchar(max)
)
insert 
into    @sqlStatements
select  'QUERY PART'
from    table

set @totrow = @@rowcount
set @currow = 1
while @totrow > 0 and @currow <= @totrow
begin
  select @nsql = SqlStatement
  from   @SqlStatements
  where  Id = @currow

  exec @result = xp_cmdshell @nsql

  set @currow = @currow + 1
end

For the next part, does the SQL Server process has enough permission to write to the c: drive? Also, look into your message pane when you execute your code, maybe you can find something there?

What you also can do, try to execute it manually. Just get one BCP statement and execute it with the xp_cmdshell. Does it gives any errors?

Answer 2

Here is my final working procedure and format file. I was not able to find the finer details of BCP commands, permision settings and format file layouts in one place, so maybe this will be of use to someone.

CREATE PROCEDURE [dbo].[ImgExport] 
   @OutputFilePath VARCHAR(500) = 'C:\SQLTest\ '
AS
BEGIN
   DECLARE @totrow int
   DECLARE @currow int
   DECLARE @result int
   DECLARE @nsql nvarchar(4000)
   DECLARE @sqlStatements table (ID int IDENTITY(1, 1),  SqlStatement varchar(max))   

   INSERT
   INTO @sqlStatements
   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 -S localhost\SQLEXPRESS2008 -T -f C:\SQLTest\Images.fmt'
   FROM dbo.Photograph  

   SET @totrow = @@ROWCOUNT
   SET @currow = 1
   WHILE @totrow > 0 and @currow <= @totrow
   BEGIN
      SELECT @nsql = SqlStatement
      FROM @sqlStatements
      WHERE ID = @currow
      EXEC @result = xp_cmdshell @nsql
      SET @currow = @currow + 1
   END
END    

Format file:

9.0  
1  
1       SQLBINARY       0       0       "\t"     1      Photograph_Data                                  ""

I hope that helps somebody.