Problem with ASP upload / query of an Excel file

Not really sure if it is an ASP, IIS or driver problem

[MozMan]

I'm having a really bizarre problem... let me give you some background info first:

Web server running IIS 5.0 (Win 2000 Adv Server platform)

Multiple ASP applications (web sites) with SQL Server 2000 back-ends (on a separate box)

Each website has a page that lets the user upload an Excel file. The file is placed in a specific directory, the ASP creates a connection to the Excel file, queries all of the data out of it, and uses that data as parameters for stored procedure calls to a SQL Server DB.

I start the web applications, all works perfectly for about 24 hours.

The problem:

Then it breaks and I get errors when it tries to connect to the Excel file. The errors range from denied permissions of the registry key for the Excel driver (fixed by granting permission exclusively in the registry; but I really didn't like doing that) to "unable to find installable ISAM," which I can't fix...

But if I stop and restart IIS, then it works again for another 24 hours or so... but I can't keep stopping production systems and restarting them every time this process fails.

Has anyone ever come accross something like this? Any idea what the problem could be? Do I need to install Excel on the server itself, or should the driver be enough?

Any input would be greatly appreciated, I'm really pulling my hair out.

[mattglet]

It doesn't sound like you need to install Excel, as it does actually work (albeit for a short amount of time).

Are you closing out your connections to the Excel files after you are done querying them? Make sure you are destroying all your objects, and all other references to the file.

[MozMan]

Well, given the newest error today:

Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
[Microsoft][ODBC Excel Driver] System resource exceeded.

It would seem that the objects are NOT being closed, however I clearly have the close statements:

objCnnSQL.Close
objRStExcel.Close
objCnnExcel.Close

Set objCnnSQL = Nothing
Set objRStExcel = Nothing
Set objCnnExcel = Nothing

I'm beginning to think they are persisting anyway, but I'm not sure why. Either that, or I have some kind of memory leak on the server...

[mattglet]

Is there anything in Task Manager that could provide a clue? Is there a process with an exceptionally large amount of RAM usage?

[MozMan]

Other than Windows? :)

No, there doesn't seem to be anything odd like that...

[tomasz]

I had similar problem and I ended up only allowing comma delimited file type and then using File System Object walk thru the file and do insert.

Another option you may have build DTS on SQL server to import the data and schedule it once a day or every xx minutes

[MozMan]

Yeah, using a DTS is on the table as an alternate solution.

I'm also looking into migrating to Windows 2003 server with IIS 6, or maybe using an Active X control so I can query the file client-side and pass the data in the form collection.

I just wish I knew what the problem was... I hate having it get the best of me like that.

Thanx guys, for the input. It is much appreciated.