Export binary files (images) from SQL using a stored procedure

Question

I am trying to export a fairly large number of image files that are stored inside a SQL database as binary data.

Being fairly new to writing stored procedures in SQL, I came across several very useful tutorials on how to archive this, but there seems to be something missing.

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

Here is the part of the business that I still have:

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 set to '1' (unsuccessfully) after calling xp_cmdshell. All table names / fields are correct, so I suspect that something is wrong with my BCP call, but I'm not sure what to do next.

Any help or advice would be greatly appreciated.

Answer 1

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

One of the worst things about cursors is that they can lock your table. What I always do for these purposes (and it's pretty fast), 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 

In the next part, does the SQL Server process have sufficient permission to write to the c drive: Also, look in the message bar when you execute your code, maybe you can find something there?

What you can do as well, try doing it manually. Just get one BCP statement and execute it using xp_cmdshell. Does this give any errors?

Answer 2

Here is my final working procedure and format file. I could not find the finer details of BCP, permission settings, and layouts of format files in one place, so maybe this will be useful 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 

File format:

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

I hope this helps someone.