How to Export a MS SQL Image column to a file.

115 pts.
Tags:
BLOB
Image databases
SQL
SQL 2005
I have pictures stored in a Ms Sql table in an image data type column. I want to be able to write the image(which is a JPG to a file. Is there a function in MS SQL 2005 or is another tool necessary?

Software/Hardware used:
MS SQL 2005

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no “native” SQL to write an image to a file (or to read/write files of any type).
You can read/write files from T/SQL by enabling OLE Automation or by using clr.

If you are trying to keep all your code in a stored procedure, then OLE Automation is probably easiest.

First – you need to enable the extended stored procedures (you may need your DBA to do this if you don’t have sufficient privileges):
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO

Then, reading/writing is just using the sp_OAMethod and sp_OASetProperty method, as in:
DECLARE @objStream INT
DECLARE @imageBinary VARBINARY(MAX)
DECLARE @filePath VARCHAR(8000)
EXEC sp_OACreate ‘ADODB.Stream’, @objStream OUTPUT
EXEC sp_OASetProperty @objStream, ‘Type’, 1
EXEC sp_OAMethod @objStream, ‘Open’
EXEC sp_OAMethod @objStream, ‘Write’, NULL, @imageBinary
EXEC sp_OAMethod @objStream, ‘SaveToFile’, NULL,@filePath, 2
EXEC sp_OAMethod @objStream, ‘Close’
EXEC sp_OADestroy @objStream

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following