How to export image field to file?

-- Write all database images (jpg) to file.--------- --------- --------- --------- --------- --------- --------- DECLARE CURSOR_ProductIds CURSOR FOR (SELECT ImgImagesId FROM ImgProductSample)DECLARE @ProductId INT;OPEN CURSOR_ProductIdsFETCH NEXT FROM CURSOR_ProductIds INTO @ProductIdWHILE (@@FETCH_STATUS <> -1)BEGIN  DECLARE @ImageData varbinary(max);  SELECT @ImageData = (SELECT convert(varbinary(max), ImageData, 1) FROM ProductImages WHERE Id = @ProductId);  DECLARE @Path nvarchar(1024);  SELECT @Path = 'C:\MyImages\Output';  DECLARE @Filename NVARCHAR(1024);  SELECT @Filename = (SELECT ImageFilename FROM ProductImages WHERE id = @ProductId);  DECLARE @FullPathToOutputFile NVARCHAR(2048);  SELECT @FullPathToOutputFile = @Path + '\' + @Filename;  DECLARE @ObjectToken INT  EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;  EXEC sp_OASetProperty @ObjectToken, 'Type', 1;  EXEC sp_OAMethod @ObjectToken, 'Open';  EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;  EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;  EXEC sp_OAMethod @ObjectToken, 'Close';  EXEC sp_OADestroy @ObjectToken;  FETCH NEXT FROM CURSOR_ProductIds INTO @ProductIdENDCLOSE CURSOR_ProductIdsDEALLOCATE CURSOR_ProductIds-- Make sure the following statement is executed to enable file IO-- From http://msdn.microsoft.com/en-us/library/ms191188.aspx--------- --------- --------- --------- --------- --------- --------- sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GO

From mkader at codeproject:

EXEC sp_configure 'show advanced options', 1;  GO  RECONFIGURE;  GOEXEC sp_configure 'xp_cmdshell',1  GO  RECONFIGURE;  GO

Then dump a format file:

Declare @sql varchar(500)SET @sql = 'bcp DBName.dbo.tblTableName format nul -T -n -f C:\testblob.fmt -S ' + @@SERVERNAMEselect @sql EXEC master.dbo.xp_CmdShell @sql 

My format file looked like this:

10.0121       SQLINT              0       4       ""   1     my_tablecol_id                                  ""2       SQLINT              1       4       ""   2     my_tablecol0                                   ""3       SQLCHAR             2       256     ""   3     my_tablecol1                                SQL_Latin1_General_CP1_CI_AS4       SQLCHAR             2       256     ""   4     my_tablecol2                                SQL_Latin1_General_CP1_CI_AS5       SQLCHAR             2       256     ""   5     my_tablecol3                         SQL_Latin1_General_CP1_CI_AS6       SQLCHAR             2       2048    ""   6     my_tablecol4                                SQL_Latin1_General_CP1_CI_AS7       SQLIMAGE            4       0       ""   7     my_imagecol                                     ""8       SQLCHAR             2       2048    ""   8     my_tablecol6                            SQL_Latin1_General_CP1_CI_AS9       SQLINT              1       4       ""   9     my_tablecol7                                      ""10      SQLINT              1       4       ""   10    my_tablecol8                                    ""11      SQLINT              1       4       ""   11    my_tablecol9                               ""12      SQLBIT              1       1       ""   12    my_tablecol10                             ""

I then edited the format file like so:

10.011       SQLIMAGE            0       0       ""   1     my_imagecol                                     ""

And then ran this SQL in SSMS:

Declare @mynum intDeclare @sql varchar(500)SET @mynum = 49 -- something meaningful only to meSET @sql = 'BCP "SELECT my_imagecol FROM DBName.dbo.tblTableName where my_tablecol_id=@mynum" QUERYOUT C:\myfilename.docx -T -fC:\testblob.fmt -S ' + @@SERVERNAMEEXEC master.dbo.xp_CmdShell @sql

This worked really well, any kind of data in the image column works.

-- Write database image (jpg) to file-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754--------- --------- --------- --------- --------- --------- --------- DECLARE @ImageData varbinary(max);SELECT @ImageData = (SELECT convert(varbinary(max), ImageData, 1) FROM ProductImages WHERE Id = 1);DECLARE @Path nvarchar(1024);SELECT @Path = 'C:\MyImages\Output';DECLARE @Filename NVARCHAR(1024);SELECT @Filename = (SELECT ImageFilename FROM ProductImages WHERE id = 1);DECLARE @FullPathToOutputFile NVARCHAR(2048);SELECT @FullPathToOutputFile = @Path + '\' + @Filename;DECLARE @ObjectToken INTEXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;EXEC sp_OASetProperty @ObjectToken, 'Type', 1;EXEC sp_OAMethod @ObjectToken, 'Open';EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;EXEC sp_OAMethod @ObjectToken, 'Close';EXEC sp_OADestroy @ObjectToken;-- Make sure the following statement is executed to enable file IO-- From http://msdn.microsoft.com/en-us/library/ms191188.aspx--------- --------- --------- --------- --------- --------- --------- sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GO

PS - The link to article 'Reading and Writing Files in SQL Server using T-SQL' was useful but I after I got the piece of code working it produced an invalid image file.