How to Export a MS SQL Image column to a file.
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
Software/Hardware used:
MS SQL 2005
ASKED:
March 16, 2010 3:36 PM
UPDATED:
March 17, 2010 7:21 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
