Saving Binary Files to SQL2005 - inconsistent results

[mushin]

My application retrieves files of type doc,pdf,rtf,xls,zip,wmv,,avi and all still images for use
and subsequent storage to the data base using vb.net 2005.

The app works well for many of these, however some do not.

I get truncation warnings when attempting to save some of these file types, all with the same code.

(These Binary files are store into a varBinary(max) column with a supposed limit of 2 gig.)

None of these files are more than 1-2 mb.

My question is this:

Why would any of these files not store correctly if handled as an array of bits read from a binary reader object and stored to the blob column?

Are not all files equal at the level of bytes?

There are hundreds of pdf,doc,jpg etc files stored fine, but not one xls,avi or wmv will store correctly.

Zip files store but cannot be read again.


Is there any other special processing required for these file types and if so why?


This is driving me nuts since the app works so well for many binary file types, yet not others.

Any thoughts ????????

 

[Catadmin]

Are they getting stored into the same table?

If so, the problem may simple. When inserting data into a table (or updating the table) SQL Server looks at the size of the largest record in the table to determine if there's "room" for the insertion/update. If the biggest record is close or at the 2 GB limit, then it's going to throw a truncation error and not insert even if your files are small enough to go in.

This one has driven me nuts before on Updates. You might have to add the record to a temp table, pull all the other data into a temp table, truncate the real table and re-insert the data whole-hog. I don't know if it'll work, but it's worth a shot.

You might also see if you can find the offending record.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"