SQL query image column to file

Question

I want to run a query over a table in SQL Server to save out the data as files.

The table has one column with a filename in it and one column that is an image column with binary file contents data in it.

I'm sure I saw some syntax that would let me do this, but I cannot for the life of me find it anymore.

Is this possible?

Answer 1

You can do this with the bcp.exe from the command line which you could call through xp_cmdshell.

bcp "select MyBlobField from myTable WHERE a=b " queryout "c:\MyImage.jpg" -T -n

You can probably do it through OLE automation natively in SQL Server; but its not something I have tried.

An easy alternative is (if you have 2005/8) a CLR into the DB to do the job. THere are lots of code examples on the web how to do that.