Select IMAGE datatype

Question

Select IMAGE datatype

brekher

SSCommitted

Points: 1514
More actions
March 5, 2012 at 1:24 pm

#258121

Hi,
I have a table which stores pictures as an IMAGE datatype and I need to select each one into a seperate file named column_name.jpg. I have 2400+ rows in total. I have been looking at doing this using sp_OACreate and sp_OAMethod but have not been successful. Does anyone have a solution? Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

This is tricky at best to do with straight sql. Typically it is much easier to do this with an application. Why do you want to take 2400 images that are tucked away and write them all to a file?

Also, in case you are not aware the image datatype is deprecated. You should instead use varbinary(max), or even better if you can upgrade to 2008 you can use FILESTREAM.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323482 More actions · Answer 2

can you add a CLR?

Elliot Whitlow, a contributor here, has a very nice CLR project for file manipulation;

http://nclsqlclrfile.codeplex.com/

the code example like this works great, you cna see how you could parameterize it in a loop, or generate the code for all the files at once:

:

--MSPSaveFileImage

-- Parameters: @FilePath,@FileName,@FileBytes

-- purpose: given an varbinary image column in a table, write that image to disk

-- usage:

declare @myfile varbinary(max)

SELECT @myfile = rawimage FROM myImages WHERE id = 1

EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.gif',@myfile

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

brekher SSCommitted Points: 1514 More actions · Answer 3

I actually need to generate 2400 files. Each named from a value taken from another column in the same table and ending in .jpg

brekher SSCommitted Points: 1514 More actions · Answer 4

Thanks Lowell!! I will give this a try in a loop and see where it takes me.