ChrisMurray ChrisMurray - 6 months ago 28
SQL Question

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

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

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?