SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Write blob field to Windows File system


Write blob field to Windows File system

Author
Message
billyusa
billyusa
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 16
I am new to SQL Server. I have a jpeg stored in varchar(max) field in SServer Table. I need to use TSQL to write the jpeg to windows file system.

Please help.
Oliiii
Oliiii
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 777
I guess you meant a varbinary(max) column?

As far as i know you can't do it in TSQL only, you would need to use a tool or an application to read it from the table and write it to disk, or you could write a CLR that does just that.

You can give it a shot with bcp.exe, but it might write extra stuffs (like header info, length, etc...) and you might not end up with just the JPG.
billyusa
billyusa
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 16
Is there no built-in procedure/function in Sserver 2005 that I can call from TSQL that can take a jpeg stored in a table and write to Windows File system?

Please help,....... anyone
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37389 Visits: 9268
The best I can think of is to use SSIS to write the object to a new file. I haven't done it before, but I'm guessing you'll need an OLE DB connection manager, a File connection manager, a DataFlow Task, and then the source and destinations.

Beyond that, I'm only guessing. You'd have to play with it.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2727 Visits: 3671
Here's a quick and dirty VB Script I munged together from the interwebs to pull out Image data from a SQL table and write the stored PDF files to disk. Edit heavily. You'll need to enter your server and DB names into the SQL connection string.


' VBS to read a BLOB (actually Image) column in SQL Server and save each row's image to a file.
' Example here reads PDFs stored in DocImage.Image column and saves each to a PDF file.

Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

outPath = "C:\Images"

Set rs = CreateObject("ADODB.Recordset")

cn = "Provider=SQLOLEDB;Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=SSPI;"
sql = "SELECT s.LastName + '_' + s.FirstName + '_' + CAST(di.DocTrackID AS VARCHAR(20)) As strFileName, Image " & _
"FROM DocImage di INNER JOIN " & _
"DocTrack dt ON di.DocTrackID=dt.DocTrackID INNER JOIN " & _
"Student s ON dt.StudentUID=s.StudentUID " & _
"WHERE ContentType='application/pdf'"

rs.Open sql, cn

While Not rs.EOF
fOut = outPath & "\" & rs.Fields("strFileName").Value & ".pdf"
Set stream = CreateObject("ADODB.Stream")
With stream
.Type = adTypeBinary
.Open
.Write rs.Fields("Image").Value
.SaveToFile fOut, adSaveCreateOverWrite
.Close
End With
rs.MoveNext
Wend

rs.Close


Hope this helps,
Rich
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37389 Visits: 9268
Please post whether or not Rich's solution helps you out. It looks interesting.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
billyusa
billyusa
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 16
I am checking out the solution. Will give an update soon.
SP Chua
SP Chua
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 84
I want to develop a system that required to store a doc / pdf file into DB, and retrieve it back and save into disk.
What is the best way?
Oliiii
Oliiii
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 777
You should open a new topic and not hijack a one year old one Smile

Any .net application can read/write to varbinary(max) field so you have a lot of choices on how to do that.
It's also possible with a SSIS package but it might require some scripting.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search