SQLTeam.com | Weblogs | Forums

Export Images from MS Sql 2000 version 8 to file system


#1

Hi All,

I am facing an issue while I am trying to export the image file (i.e jpeg, jpg, png) from Sql 2000 to file system. This is MS SQL 2000 version 8 running on Windows Server 2003 R2.
I am new to this project and I don't have much idea on MS SQL.

I have table called Image conatin below field
image_id - data type INT
Picture data type IMAGE

I have tried the below but getting error.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;

but it gives me error like this doesn't exist or this is in advanced option.

Please suggest how can i solve this issue.


#2

Pretty sure that was introduced in SQL 2008 ... what does:

EXEC sp_configure

show? If "Ole Automation Procedures" is not in the list then your version doesn't support that, sorry.


#3

Hi Kristen,

While i run EXEC sp_configure, it doesn't show the Ole Automation Procedures.

Can you please suggest how can I export the images.


#4

Sorry, no idea. What does Google suggest?


#5

Haven't found any good suggestion from google. Some site suggested to develop a front end application using asp.net or VB that might help, but its time consuming.


#6

That sounds like a fair bit of work :frowning:

We never saw the benefit of storing Images in the DB (rather than just saving them in the file system,and storing Path / Filename in the DB) UNLESS there was a need to have an Image AND some Other Data inserted in a single, atomic, transaction - i.e. you either got both the image AND the associated data, or NEITHER.

It was a popular thing to do though, back then ... maybe it still is.

Sorry, I don't know of any tricks to help with this, but it would be helpful to hear back how you get on, if only to help other folk that come along later.

Perhaps just have a VB loop that pulls the Image and saves it to disk is "reasonably trivial", but my recollection is that you had to read those BLOB datatypes (which includes IMAGE) in "chunks" that you then had to concatenate before them being usable. If the BLOB can be massive (are your images huge?) that can be quite a challenge, and you then wind up having to STREAM the data out instead which, whilst not difficult, is someone different to normal stuff, and therefore unlikely there is anything "lying around" that can be used as a starting point. So you have to discover all the pitfalls ... and solve them yourself :frowning:


#7

http://stackoverflow.com/questions/15495925/how-to-save-all-sql-server-image-datatype-to-file-system-with-correct-file-ext
http://stackoverflow.com/questions/1366544/how-to-export-image-field-to-file
http://www.bing.com/videos/search?q=how+to+save+an+image+file+from+sql+server+to+the+file+system&qpvt=how+to+save+an+image+file+from+sql+server+to+the+file+system&FORM=VDRE