c# - Microsoft ACE OLEDB connection creating empty Excel when there are 166,110 rows

Question

I am programming in C# and using an oledbconnection. This is the standard connection string e.g.

using (OleDbConnection conn = new OleDbConnection(
          "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
           saveFilenameAndLocation + 
          ";Extended Properties='Excel 12.0 Xml;HDR=Yes'"
      ))  

This works successfully and creates my Excel spreadsheet full of the correct data, when the number of rows isn't excessive. However, whenever the number of rows in the spreadsheet increases to a large size (e.g. currently it is failing on 166,110 rows), it produces an empty spreadsheet with the worksheet tab name set to A266FF2A662E84b639DA.

It is not possible to rewrite this so that it doesn't use an OLEDB connection, any ideas why it doesn't work when the row size increases?

Answer 1

I am not sure about your application environment, but I have seen this when generating Excel files from an ASP.NET app.

Once the amount of data exceeds a certain size (~1 MB in my experience), the provider will attempt to create a temp file while generating the output. I have come across this issue using the 32-bit provider on 64-bit systems. If you are running under a service account with this configuration then the location where these files are created is

C:WindowsSysWOW64configsystemprofileAppDataLocalMicrosoftWindowsTemporary Internet FilesContent.MSO

This location, however, is only accessible to administrators and SYSTEM by default, and if the provider is running under a non-privileged account and is unable to create the temp file it will fail silently and just return its default "empty file" with the A266FF2A662E84b639DA worksheet.

What you need to do is grant the account of the application that runs under (for example Network Service or IIS AppPool) read/execute/list contents permissions along the path "C:WindowsSysWOW64configsystemprofileAppDataLocalMicrosoftWindowsTemporary Internet Files" and then full access to the Content.MSO folder itself.

If the provider matches the bitness of your system then I suspect you need to perform the process above for C:WindowsSystem32configsystemprofileAppDataLocalMicrosoftWindowsTemporary Internet FilesContent.MSO instead, although I have never tested this.

I want to extend my thanks to sysinternals for providing procmon that helped me troubleshoot this issue.