x

Export Images from SQL SERVER 2008

Hello ,

Thanks for my previous queries answers.

Please let me know how to export the images from sql server 2008.

Thanks Naina

more ▼

asked Mar 02, 2011 at 08:45 PM in Default

avatar image

Naina_S
2 15 15 20

Please provide some more information about what it is you're trying to do. Each question should provide enough information, without having to look into other questions. It will be really hard to follow the question and answers otherwise.

I have looked at your other question about storing images in SQL Server, and you didn't provide much feedback to the answers given there. Therefore I don't know how you solved that part (varbinary(MAX), FILESTREAM, storing actual images outside of the DBMS etc).

Mar 02, 2011 at 11:10 PM Magnus Ahlkvist

to which db or images to applicaion

Mar 02, 2011 at 11:12 PM ramesh 1
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you have eg. a table in which you have a column with binary data in which are stored images. and eg. a column with FileName.

 CREATE TABLE ImageData(
     FileName varchar(255),
     ImageData varbinary(MAX)
 )

And if you want to extract all images into a particular folder disk into separate files, then the easiest way is to create a SSIS package for that.

In the data flow task, put an OLE DB source and for reading the data from the table. Then put a Script component and select to use it as Destination. Connect the Source to that script component, select the input columns you want to use (the FileName and ImageData).

Then click the Edit Script button and provide a code for the script to store the data into a file on disk. Then in the editor complete the ProcessInputRow method like the one below

 public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
     File.WriteAllBytes(Row.FileName, Row.ImageData.GetBlobData(0, (int)Row.ImageData.Length));
 }

This assumes, that the FileName contains a completa path. If it contains only a filenames, then you can add a path to it.

 public override void Input0_ProcessInputRow(Input0Buffer Row)
 {
     File.WriteAllBytes(Path.Combine(@"C;\folder", Row.Filename), Row.data.GetBlobData(0, (int)Row.data.Length));
 }

And that's all. Once you run the packages, you will have all the images in separate files. if you do not have a file names, you can eg. create an incremental numbering etc. There are a lot of possibilities.

more ▼

answered Mar 02, 2011 at 11:41 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2221
x1208

asked: Mar 02, 2011 at 08:45 PM

Seen: 1800 times

Last Updated: Mar 02, 2011 at 08:46 PM

Copyright 2018 Redgate Software. Privacy Policy