Copy all immages out of SQL Server with TextCopy

Question

Post reply

Copy all immages out of SQL Server with TextCopy

runelords

Ten Centuries

Points: 1275
More actions
March 17, 2006 at 9:20 am

#91609

I have found some scripts that allow me to use TextCopy to copy a single image out of SQL Server into a folder on my hard drive. But I am looking for an example on how to export server images out of SQl Server into indvidual files. Any help woould be greatly appreciated.

Arthur Lorenzini

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

this should really be handled by a programming language and not SQL server.

it's very easy, maybe 15 lines of code to loop thru a recordset and save the image to file.

here's one of many links with code samples to do it:

http://www.freevbcode.com/ShowCode.Asp?ID=1802

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

runelords Ten Centuries Points: 1275 More actions · Answer 2

Thank you for your reply. After a little bit more research I came up with this which seems to work:

Dim con As New SqlConnection _

("Server=ServerName;uid=username;pwd=password;database=databasename")

Dim sSQL As String = _

"Select id, image, imagetype " & _

"FROM Photosubmissions WHERE ImageType <> " & "'UNKNOWN '"

Dim da As New SqlDataAdapter _

("Select id,image, filename, imagetype From PhotoSubmission WHERE ImageType NOT LIKE " & "'UNK% '" & "Order By Filename", con)

Dim cbPhotos As SqlCommandBuilder = New SqlCommandBuilder(da)

Dim ds As New DataSet

Dim i As Integer

con.Open()

da.Fill(ds, "imgPhotos")

Dim drPhoto As DataRow

drPhoto = ds.Tables("imgPhotos").Rows(0)

For Each drPhoto In ds.Tables("imgPhotos").Rows

Try

Dim bData() As Byte

Dim sFilename As String

Dim sFileID As String

Dim sPath As String

Dim sFileType As String

Dim sNewFileName As String

bData = drPhoto("image")

sFileID = drPhoto("Id")

sFilename = drPhoto("filename")

sFileType = drPhoto("ImageType")

sPath = "C:\tmp\"

Dim K As Long

K = UBound(bData)

sFilename = sPath + sFileID + "-" + Trim(sFilename) + "." + sFileType

sNewFileName = Mid(sFilename, 1, InStrRev(sFilename, ".") - 1)

Dim fs As New FileStream _

(Trim(sNewFileName), FileMode.OpenOrCreate, _

FileAccess.Write)

'(sPath + sFileID + "-" + Trim(sFilename) + "." + sFileType, FileMode.OpenOrCreate, _

fs.Write(bData, 0, K)

fs.Close()

Catch ex As Exception

MsgBox("Images Exported!")

cbPhotos =

Nothing

ds =

Nothing

da =

Nothing

con.Close()

con =

Nothing

Exit Sub

End Try

Thanks again.

Arthur Lorenzini