How can I export a SQL Server BLOB field to a standard Windows JPG file?

I would suggest you to do converting blob to JPG from SQL Server itself. you can use following code in SP or anywhere else to do it right from SQL Server

-- Using TextCopy in MSSQL$instancename\Binn for retrieving image.
 
--0. xp_cmdshell is turned on from sp_configure
sys.sp_configure xp_cmdshell, 1
RECONFIGURE;
 
GO
-- 1 Declaration
DECLARE @sql VARCHAR(500),
@server sysname,
@user sysname,
@pwd sysname,
@db sysname,
@table sysname,
@column sysname,
@whereclause VARCHAR(500),
@filename VARCHAR(100),
@filepath VARCHAR(500)
 
--2. Config
SET @server = @@SERVERNAME
SET @user =  input login here
SET @pwd =  and pwd
SET @db = DB_NAME()
SET @table = ImageTest
SET @column = Image
SET @whereclause = WHERE ID = 13
SET @filename = ImageFromSQL.jpg
SET @filepath = C:\tmp\
 
--3. Set textcopy path from path environment variable(manually)
--And set sql string
SET @sql= TextCopy  +
 /S  + @server +
 /U  + @user +
 /P  + @pwd +
 /D  + @db +
 /T  + @table +
 /C  + @column +
 /W  + @whereclause +
 /F  + @filepath + @filename +
 /O  +
 /Z   for debuging
 
EXEC master..xp_cmdshell @sql --,no_output if wanna see the debug
GO

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:

if you wish to do it from C# code, than do follow the link below

http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html

sorry, in my script in above post, I am seeing some junk character, here is the copy of the same.

-- Using TextCopy in MSSQL$instancename\Binn for retrieving image.
 
--0. xp_cmdshell is turned on from sp_configure
sys.sp_configure xp_cmdshell, 1
RECONFIGURE;
 
GO
-- 1 Declaration
DECLARE @sql VARCHAR(500),
@server sysname,
@user sysname,
@pwd sysname,
@db sysname,
@table sysname,
@column sysname,
@whereclause VARCHAR(500),
@filename VARCHAR(100),
@filepath VARCHAR(500)
 
--2. Config
SET @server = @@SERVERNAME
SET @user = -- input login here
SET @pwd = -- and pwd
SET @db = DB_NAME()
SET @table = 'ImageTest'
SET @column = 'Image'
SET @whereclause = 'WHERE ID = 13'
SET @filename = 'ImageFromSQL.jpg'
SET @filepath = 'C:\tmp\'
 
--3. Set textcopy path from path environment variable(manually)
--And set sql string
SET @sql= 'TextCopy ' +
' /S ' + @server +
' /U ' + @user +
' /P ' + @pwd +
' /D ' + @db +
' /T ' + @table +
' /C ' + @column +
' /W ' + @whereclause +
' /F ' + @filepath + @filename +
' /O ' +
' /Z ' -- for debuging
 
EXEC master..xp_cmdshell @sql --,no_output if wanna see the debug
GO
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:

Hi all,

Thanks so much for these ideas.  It'll be a week or so before I am able to work further on this issue.  I'll send an update at that time.

Thanks!

puppydogbuddy:

Thanks for those links!  I found Leban's A2KLoadSaveJpeg Access application very interesting.  The main difference between this application and my need, is that I would like to process a table that has a BLOB field in each row, and automatically (in batch) have the program read each row, convert the BLOB field to a JPG and write out the JPG.  (The name for each JPG is contained as a field in the same row as the BLOB).  So, in other words, I need the process to run "lights out", once an operator clicks a button on an Access form to kick it off.  Is it easy to convert a BLOB to JPG directly, without going through an image control in Access?

Thanks!

GLFord
AFAIK, with BLOB you can only input/output binary "chunks" (see link below).  Other than Access 2007 (stores jpg directly) or Leban's method, compessed >>>storage<<< for OLE not available in Access.  

see :  http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q103257&
Excerpt from prior posted link to Jamie's software:
   On a final note, of the three techniques discussed, I prefer to use the last technique as the images are neatly stored away inside the database file and we do not have to suffer the bloat associated with the first. For a working example of storing images as binary large object bitmaps (BLOB) in an OLE field, please feel free to download and evaluate my Image Albums application. A free example of storing files in this way using DAO with viewable source code can be found by downloading the getBinary zip file, an Access 97 file that can also be converted to Access 2000 or 2002 depending on which version you are running.

The final technique can be tricky to set up but has the advantage of storing images inside the database, meaning that password protection is possible and also avoids the bloat associated with the first technique, as the images are stored reflecting their true size. For this technique I would again recommend using the "file open" dialog, allowing the user to browse to an image. We can use either DAO or ADO (in Access 2000 or 2002) to grab the image file and read it into the OLE field in chunks. To display the image we have to extract it from the database to a temporary file and like the second technique, use an image control to display it by setting the control's "picture" property to the path of the temporary file. Once we are finished with the temporary file we can use the VBA "kill" command to delete it. Unfortunately, as with the second technique, the same problem with Access continuous forms exists. If displaying the images using single forms is not adequate you could take the time to create a customized form to display more than one individual image, although unfortunately this task is made awkward by the fact that Access does not support control arrays. In addition, this technique also incurs a marginal overhead when compared to the second, as the image files first have to be extracted from the database before we can view them.

I'm making some progress... will update this question next week with the results.

Thanks to all!