OleDB connection to excel file fails ONLY in production

Question

I have a VB application using .NET 3.5 that allows users to upload an Excel file. The application then reads from this file and inserts its contents into a database.

The problem is that the read seems to fail in our PRODUCTION environment only. Here's the error produced:

ErrorMessage: System.Data.OleDb.OleDbException: Unspecified error
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at uploader.ReadExcel(String sFile) in E:\path_to_application\uploader.aspx.vb:line 85

Here's lines 84 and 85 of the ReadExcel function in uploader.aspx.vb:

oConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFile + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
oConn.Open()

oConn.Open() fails ONLY in production, the exact same codebase on my local machine works as expected.

The file does indeed exist at the path specified in the sFile variable
This question suggested it might be a problem with exceeding the concurrent connection limit, but even after restarting the application with IIS the problem persisted
It does not seem to be a permissions issue, all user groups on the server have at least Read & Execute on the uploaded files

What other differences could I look for between the production and local environments? What else could cause opening this OleDB connection to fail like this?

UPDATE: A workaround seems to be to recycle the application pool in IIS. Any ideas on what it is that's being reset that fixes it?

Is the application pool for your application running as a defined windows account, or as a default application pool user? — Jon Egerton, Jan 15 '13 at 15:13
It is a defined windows account and a member of the Guests group, which seems to have Read & Execute permissions on the directory in question. — Zach Esposito, Jan 15 '13 at 16:12
Using that same account, are you able to open an ODBC connection to the excel file via the "Data Sources (ODBC)" dialog in Administrative Tools (do this test on the console of the webserver if poss) — Jon Egerton, Jan 15 '13 at 16:31
@JonEgerton Unfortunately I don't have access to the account that the application pool is running as — Zach Esposito, Jan 15 '13 at 19:15

score 1 · Answer 1 · answered Jan 15 '13 at 17:34

1

I have not used OleDb myself, but I have worked with the Jet driver. It is very important to have the proper version of the driver for the OS environment. For some time there was no 64 bit driver, but they released one in 2010. You can download it here:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

Another option may be to run the application in 32-bit mode. It's also possible that this is not the problem, but based on your results it does seem quite likely.

answered Jan 15 '13 at 17:34

Michael Richardson

4,213
2
31
48

This is an important point, but in my case both machines are 32-bit. – Zach Esposito Jan 15 '13 at 19:17
That makes things simpler. I assume that you already checked if the driver is installed? I think that it's typically installed with Microsoft Office, but I'm not certain. – Michael Richardson Jan 15 '13 at 20:45

score 0 · Answer 2 · answered Jan 15 '13 at 17:12

0

probackpacker is going to say that if your application is in a 64 bit environment it wont work, because there is no 64 bit OLEbd drive

answered Jan 15 '13 at 17:12

China Syndrome

953
12
24

OleDB connection to excel file fails ONLY in production

2 Answers2