Storing Objects in SQL Server 2000

I have an ASP.NET 1.1 application that stores documents and files inside SQL Server 2000. I am able to upload and store any type of file in both my developmnt environment (XP Pro SP3) and on my Windows 2003 Server. On my client's site they are able to upload and store all document types except .BMP, .ZIP and .VSD files.

My code (see attached) simply streams the data into a SQL Server column with a data type of Image and does not care what type of file it is.

Can anyone give me ANY reason why certain file types would be excluded? For example, are there any policies at an Operating System level that could be set/unset to overcome this issue? The collation sequence on all instances of SQL Server 2000 is identical across all sites.

.Parameters.Add("@FileContent", SqlDbType.Image)
.Parameters.Item("@FileContent").Value = GetByteArrayFromFileField(FileUpload)

    Private Function GetByteArrayFromFileField(ByVal FileField As System.Web.UI.HtmlControls.HtmlInputFile) As Byte()
        ' Returns a byte array from the passed 
        ' file field controls file
        Dim intFileLength As Integer, bytData() As Byte
        Dim objStream As System.IO.Stream
        If FileFieldSelected(FileField) Then
            intFileLength = FileField.PostedFile.ContentLength
            ReDim bytData(intFileLength)
            objStream = FileField.PostedFile.InputStream
            objStream.Read(bytData, 0, intFileLength)
            Return bytData
        End If
    End Function

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alagurjanTechnology LeadCommented:
Reg .vsd file Its very easy to store files in SQL server 2000 using image data type and there is not restriction on it like file Type, but you must check the file size and column size which you have created in database.

Reg .ZIP file you can use VARBINARY(MAX)  to store it but still you can use image data type also but it is less in performance.
Anthony PerkinsCommented:
Since there is no built in limitation in SQL Server (although IMHO it is a very bad idea) it has to be some company policy they have set in IIS.
VARBINARY(MAX) won't work on SQL2000
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

pstanfordDirectorAuthor Commented:
Thanks all for your prompt response. I'm not going to accept any of these potential solutions for the time being as my client has now closed down for 2 weeks over the Christmas holiday period and I do not have remote access to their server to investigate further until they return. With regard to storing documents inside the database, it turned out to be a good decision when a server crashed recently and the client (a large manufacturng company who should have known better) was only backing up the database, not the file system or even the web application. We were able to recover the database and the more than 5000 documents that would all have been lost otherwise. The files being uploaded vary between 50kb and 50mb so the column type of Image is never going to be threatened by these limits. I'll look into the IIS Policy issues as soon as posible. Thanks again for your prompt response and have a great Christmas.
pstanfordDirectorAuthor Commented:
In terms of a possible policy setting in IIS, can anyone tell me where I might find this? I can review my own server's IIS settings to test this if I know where to look. I had a quick look at the IIS Properties for the development version of the website but can't see anything obvious. I've never set any restrictions like this in IIS and the code originally posted definitely works in both my environments.

Thanks and best regards
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
<upload and store all document types except .BMP, .ZIP and .VSD files.

looks like some AD GPO or antivirus setting also check IIS

did they get any errors durin zip upload?
pstanfordDirectorAuthor Commented:
Hi all,

I'll be able to speak to the client again on Tuesday 10th January but what appears to be happening is that the posted file content (from the standard file upload dialog) is blank and is therefore not passed as a parameter to the stored procedure. From the code I originally posted you can see that there is no distinction between flle types and the code functions correctly in both my development and server environments and on different browser versions on different machines, i.e. Windows XP Pro SP3 and IE8, Windows 7 Pro and IE9. It is possible that the settings relate to anti-virus and I'll check that out next week.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pstanfordDirectorAuthor Commented:
Having now spoken to my client the problem has become very clear. There is no issue uploading files of any type based on the code I originally posted. There are no restrictions in IIS or AD and I have not changed any of the original code.

The problem occurred because the client was creating new files with no content and then attempting to upload them. I never anticipated this so was only checking for the existence of a file name, not that the file actually had any content. I've added some error handling to stop the upload before it passes a NULL value to the stored procedure.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.