none
Intermittent Issue - OleDbConnection.Open() throws System.Data.OleDb.OleDbException: Unspecified error RRS feed

  • Question

  • Aproduction application in our organization uses Excel 2003 files to handle user submitted data through a web application. This application works reliably most of the time. Recently, the application has begun intermittently throwing "System.Data.OleDb.OleDbException: Unspecified error" when the OleDbConnection.Open() method is called. The error continues until the application pool is recycled, at which time everything functions as expected again. No errors are captured within the Windows Application event log.

    The ASP.NET web application is hosted within web parts in WSS 3.0 on a Windows Server 2003 32 bit machine.

    Any insight would be much appreciated.

    We are using the following code to retrieve data from an Excel 2003 file:

    public static DataTable GetWorksheetData(string filePath) { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder { DataSource = filePath }; builder.Provider = "Microsoft.Jet.OLEDB.4.0"; builder["Extended Properties"] = "Excel 8.0;IMEX=1;HDR=YES"; DataTable table = new DataTable(); try { // Creates an OleDbConnection for the excel file using (OleDbConnection connection = new OleDbConnection(builder.ConnectionString)) { connection.Open(); string sheetName = GetWorksheet(connection, "Template"); if (!string.IsNullOrEmpty(sheetName)) { using (OleDbCommand command = connection.CreateCommand()) { try { command.CommandText = string.Format("SELECT * FROM [{0}]", sheetName); using (OleDbDataAdapter adapter = new OleDbDataAdapter(command)) using (DataSet dataSet = new DataSet()) { adapter.Fill(dataSet);        table = dataSet.Tables[0];

    } } finally { connection.Close(); } } } else { throw new InvalidWorksheetException(); } } } catch (Exception ex) { Logger.Write(LogMsgSeverity.Error, ex); throw; } return table; } private static string GetWorksheet(OleDbConnection connection, string sheetName) { string validSheetName = string.Empty; using (DataTable tables = connection.GetSchema("Tables")) { DataRowCollection rows = tables.Rows; if (rows.Count > 0) { foreach (DataRow row in rows) { if (row["Table_Name"].ToString().Contains(sheetName)) { validSheetName = sheetName; } } } } return validSheetName; }

    Thank you!

    Dylan Berry



    • Edited by Dylan C. Berry Friday, November 30, 2012 2:25 PM correction
    Thursday, November 29, 2012 8:37 PM

Answers

  • I also found this:

    Problem with OleDbConnection, Excel and connection pooling


    Well you certainly don't want connection pooling. That is, unless the number of connections in the pool is one.

    As I mentioned concurrent multi-user access is not supported by an Excel Workbook file (that means you can't allow it). It doesn't matter if it's the same user account or a different user account, multiple concurrent connections will cause a problem.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Alexander Sun Friday, December 7, 2012 1:22 AM
    Saturday, December 1, 2012 12:43 AM

All replies

  • Hi,

    Is there a chance for the excel file be opened by different process? 

    I suspect of an access concurrency problem, can you be sure of open the file in shared mode ? 

    Are your sure there are no other process writing the file while you are trying to .Open() ?


    -

    Friday, November 30, 2012 11:35 AM
  • Hi,

    Is there a chance for the excel file be opened by different process? 

    I suspect of an access concurrency problem, can you be sure of open the file in shared mode ? 

    Are your sure there are no other process writing the file while you are trying to .Open() ?


    -


    Hi Enrique,

    Thank you for your response.

    Why would we have concurrency issues for all files? Once we see the first exception, all further attempts to connect to an Excel file fails with the same result.

    I also am positive that there is no concurrency issues. The application is designed to prevent any concurrency issues. The system functional id is the only account with access to the temporary file store and there are mechanisms built in to ensure that the file cannot be overwritten by another upload during processing using a unique naming convention and upload tracking sub-system. 

    Cheers,

    Dylan
    Friday, November 30, 2012 2:37 PM
  • I see, one thing you can do is to configure the App Pool on IIS to be recycled on the first exception, maybe it helps on your issue, of course it doesnt help in finding the problem.

    The "Unspecified error" doesnt help much but usually is a problem with creating temporary files on disk, lack of space, permissions, etc. maybe you need to recycle more often to clean temp files...

    Check this cases:

    http://msmvps.com/blogs/rakeshrajan/archive/2005/07/04/56461.aspx

    If you can track the temp folder maybe you can see if its hitting a problem in the temp file creation action.

    Another more drastic option is to let OleDb in the past and use an alternative for reading excel files, in my experience NPOI Library is much more reliable than OleDb/Odbc/OleAutomation/etc. for work with excel files. (plus you dont need to install office or excel in the server).

    You would only need to replace the inner logic of your GetWorksheetData method.

    Regards


    -

    Friday, November 30, 2012 2:58 PM
  • Hi Enrique,

    Unfortunately, I am not sure I can use any of your suggestions.

    Recycling the application pool is not an option as this is part of a larger, high availability system where any hiccups during business hours would be considered a major issue.

    If the temporary file creation was the issue, the permissions would cause the exception every time (recycling the application pool would have no impact) and we have ensured there is plenty of space.

    The drastic option of moving to an open source component to read from the file would not be available to us. Open source code is HIGHLY discouraged and is heavily gated and scrutinized before use in production applications. This process is lengthy and more often than not the component is outright rejected.

    Thank you for your input and insight!

    Dylan

    Friday, November 30, 2012 3:59 PM
  • Multi-user concurrent access is not supported for an Excel Workbook when using the Jet or ACE OLEDB Providers and the Excel ISAM driver. Only exclusive access is supported.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, November 30, 2012 5:33 PM
  • Multi-user concurrent access is not supported for an Excel Workbook when using the Jet or ACE OLEDB Providers and the Excel ISAM driver. Only exclusive access is supported.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Hi Paul,

    We are using exclusive access.

    Thanks,

    Dylan

    Friday, November 30, 2012 6:46 PM
  • I understand the open source rule while i think is crap, specially if it mades you use OleDB instead of NPOI...

    Its your call. Anyway you can create some interface: IGetExcelData with the methods you post here and make your implementation with OleDb like the methods you post, if one day the error keeps arising you could have a redy to go implementation of the interface with NPOI so when the error makes a big problem and the open source rule would look silly, you can use it.

    I still think you are hitting a run-time resource limit, thats because recycling the pool solves it. Not a permission problem but maybe some handle counter in the OleDb driver. You are not going to get much "official support" for this software (the OleDb driver) as if you where using some open source with a dedicated community one.

    Regards


    -

    Friday, November 30, 2012 7:08 PM
  • Hi Paul,

    We are using exclusive access.

    Thanks,

    Dylan


    How are you implementing exclusive access? I don't see Mode=Share Exclusive in the connection string.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, November 30, 2012 8:08 PM
  • Only the application system account has access to the file and there is a mechanism built which deletes the file after retrieving the data (in a finally block in calling code). What would the Mode=Share Exclusive buy us?
    Friday, November 30, 2012 9:33 PM
  • Only the application system account has access to the file and there is a mechanism built which deletes the file after retrieving the data (in a finally block in calling code). What would the Mode=Share Exclusive buy us?

    That option allows you to limit the number of connections to one at the application level. I'm not sure but it sounds like what you are telling me is that the app is coded so that only one connection to the Excel file can occur at a time. I don't see code that limits the number of connections in your example but I will take your word for it if that is what you are telling me.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, December 1, 2012 12:31 AM
  • I also found this:

    Problem with OleDbConnection, Excel and connection pooling


    Well you certainly don't want connection pooling. That is, unless the number of connections in the pool is one.

    As I mentioned concurrent multi-user access is not supported by an Excel Workbook file (that means you can't allow it). It doesn't matter if it's the same user account or a different user account, multiple concurrent connections will cause a problem.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Alexander Sun Friday, December 7, 2012 1:22 AM
    Saturday, December 1, 2012 12:43 AM