Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Write blob field to Windows File system Expand / Collapse
Author
Message
Posted Friday, September 17, 2010 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 8:18 AM
Points: 3, 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.
Post #988357
Posted Monday, September 20, 2010 3:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:11 AM
Points: 206, Visits: 756
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.
Post #989191
Posted Monday, September 20, 2010 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 8:18 AM
Points: 3, 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
Post #989365
Posted Monday, September 20, 2010 11:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 7,025, Visits: 6,194
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #989604
Posted Monday, September 20, 2010 12:54 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 8:22 AM
Points: 685, Visits: 2,992
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
Post #989700
Posted Tuesday, September 21, 2010 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 7,025, Visits: 6,194
Please post whether or not Rich's solution helps you out. It looks interesting.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #990166
Posted Tuesday, September 21, 2010 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 8:18 AM
Points: 3, Visits: 16
I am checking out the solution. Will give an update soon.
Post #990651
Posted Wednesday, September 21, 2011 9:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 22, 2012 7:31 AM
Points: 7, 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?
Post #1179152
Posted Wednesday, September 21, 2011 11:35 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:11 AM
Points: 206, Visits: 756
You should open a new topic and not hijack a one year old one :)

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.
Post #1179174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse