September 17, 2010 at 10:09 am
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.
September 20, 2010 at 3:23 am
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.
September 20, 2010 at 7:44 am
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
September 20, 2010 at 11:24 am
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.
September 20, 2010 at 12:54 pm
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
September 21, 2010 at 6:06 am
Please post whether or not Rich's solution helps you out. It looks interesting.
September 21, 2010 at 12:23 pm
I am checking out the solution. Will give an update soon.
September 21, 2011 at 9:29 pm
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?
September 21, 2011 at 11:35 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply