CODEWITHSUNDEEP
sqlsql-serverimagesql-server-2008save
mrtsherman
mrtsherman

Reputation: 39872

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 have 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.

Upvotes: 0

Views: 3245

Answers (2)

Jimbo
Jimbo

Reputation: 2537

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

Upvotes: 0

JoeLeBaron
JoeLeBaron

Reputation: 348

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

Upvotes: 1

Related Questions