Save image to disk directly from SQL Server query

I will preface this question with the fact that I don't know if this is possible and that I don't know a ton about sql or file structures.

I h开发者_JAVA百科ave a sql server database with a column of type image. I want to save one of these images to disk without having to write a program (if possible). So in an ideal world I would run a query in Sql Server Management Studio. RMB on a result cell and save as image to disk.

This of course doesn't work, but is what I am after possible? I did search SO and Google, but couldn't find anything.

What version of SQL Server are you using? SQL 2008 supports FILESTREAM where you can save BLOB data directly to an NTFS volume. There is a good blog article about it here: http://blogs.msdn.com/b/rdoherty/archive/2007/10/12/getting-traction-with-sql-server-2008-filestream.aspx

Try this:

declare @ObjectToken int
declare @signature varbinary(8000)  --  Load @signature with the value of your image, I have not tested varbinary(max) with sp_oacreate
declare @fullname varchar(500)  --  Load @fullname with folder and filename to export to

exec sp_oacreate 'ADODB.Stream', @objecttoken output
exec sp_oasetproperty @objecttoken, 'type', 1
exec sp_oamethod @objecttoken, 'open'
exec sp_oamethod @objecttoken, 'write', null, @signature
exec sp_oamethod @objecttoken, 'savetofile', null, @FullName, 2
exec sp_oamethod @objecttoken, 'close'
exec sp_oadestroy @objecttoken