Problem extracting Excel BLOB files from SQL SERVER 2000.

[VisualGuy]

I'm having an issue when attempting to extract .xls files that are stored as BLOBs from SQL SERVER 2000. The following works perfectly for .txt and .doc files. However, when I download .xls files, they become corrupted. I'm thinking that it has to do with the fact that I'm using a BinaryWriter. Any suggestions?

Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 500
Dim outbyte(bufferSize - 1) As Byte
Dim retval As Long
Dim startIndex As Long = 0
Dim pub_id As String = ""

' Read record
Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
reader.Read()
fs = New FileStream(NetDirectory & FileName, _
FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)
startIndex = 0
retval = reader.GetBytes(0, 0, outbyte, 0, bufferSize)

' Continue reading and writing while there are bytes beyond the size of the buffer.

Do While retval = bufferSize
bw.Write(outbyte)
bw.Flush()

' Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize

'(The zero-based column ordinal, The index within the field from which to begin the read operation,

' The buffer into which to read the stream of bytes, The index for buffer to begin the read operation,

' The maximum length to copy into the buffer)
retval = reader.GetBytes(0, startIndex, outbyte, 0, bufferSize)
Loop

bw.Write(outbyte, 0, retval - 1)
bw.Flush()
bw.Close()
fs.Close()
reader.Close()
If conn.State = ConnectionState.Open Then
conn.Close()
' Dispose connection
conn.Dispose()
End If

 

[VisualGuy]

In addition, when the file is opened, EXCEL attemps to repair the file. Sometimes this doesn't work.

Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\test.xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted.

 

[PRPhx]

So, you are storing your Excel files in a db? Are you also storing them outside the db? If so, why store them inside your db? I would store the path and file name info to the spreadsheets, and nothing more.

Don't you need to start Excel itself before you read an .XLS file? And I don't see you doing this.

 

[VisualGuy]

Thanks for the response PRPhx.

What's happening is that I receive files from customers who transmit files over the web and it gets stored on my SQL database. I then download the files to our network.

I know that the files are being stored properly, because when I run the textcopy.exe SQL utility, I can download all files, including .xls files with no problem.

When I do this using VB.NET, it works fine for all types of files except .xls. It will download the .xls file, but when I go to open the file, the file requires repair, which only works some of the time.

I've tried it a couple of different ways, including this way...

http://support.microsoft.com/kb/308042/EN-US/

...but it just won't work. I'm wondering if anyone else has come across this problem.

 

[PRPhx]

I found this that might be of more help:

http://www.codeproject.com/useritems/Store_and_manipulat_BLOBs.asp

 

[VisualGuy]

Unfortunately, I need to see this in VB.NET. From what I could decifer, the big difference between what I'm doing and what's occurring in this article is that I'm using a FileStream and the author is using a MemoryStream. We're both using a binary writer. Which, really should work fine for a .xls file.

I like the FileStream, because it tells the app where to put the file on the network.

Do you know if this could be translated or if there is an identical article, but for VB.NET?