Microsoft.ACE.OLEDB.12.0 ( 64-bit ) : Insert into EXCEL .xlsm return blank sheet after 4170 rows

Question

Hi All,

I encountered the following issue with Microsoft.ACE.OLEDB.12.0 ( 64-bit ), kindly advise :

Environment :
- AccessDatabaseEngine 2010 ( x64 ) + SP1
- Windows Server 2008 R2 Enterprise ( 64-bit )
- Microsoft SQL Server 2008 R2 Enterprise Edition (64-bit)
- IIS 7.5.7600
- Simple Excel_Template.xlsm with columns layout A-AM

Scripting ( with ASP.NET - C# ) to retrieve selected data from MSSQL and insert into the excel template file.

Everything working FINE if the dataset / number of rows less than 4170. 
The scripts started not behave correctly after 417X onwards :
- No exception when debugging
- OleDbCommand.ExecuteNonQuery successful ( return indicator 1 throughout entire loops )
- If put a break at 4170 row count then excel file .xlsm was generated successfully ( file size 615 KB )
- If put a break at 4180 row count then excel file .xlsm was generated but output file is blank ( exactly same as template file before insert any row/data )
- All processes, loops and connections are working fine.
- No data or formatting issue because the remaining rows having similar length and pattern

The scripts are similar to the below :

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XXX.xlsm;Extended Properties=Excel 12.0 Xml;";

try
{
    using (OleDbConnection con = new OleDbConnection(connString))
    {
        con.Open();
        ...
        OleDbCommand cmd = new OleDbCommand(strSQL, con);
        cmd.ExecuteNonQuery();

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            ...
            cmd.CommandText = strSQL;
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}
catch (Exception ex)
{
}

Answer 1

Hi Choor,

Could you please try to insert values to Excel via SQL Server Management studio?

Since this issue is a little bit strange, also try to use "import and export Data" wizard to export data from SQL server to Excel to see if this issue still happen.

Please follow these references:

http://msdn.microsoft.com/en-us/library/ms140052.aspx

http://technet.microsoft.com/en-us/library/cc917564.aspx

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Iric Wen
TechNet Community Support

Answer 2

Hi All,

I encountered the following issue with Microsoft.ACE.OLEDB.12.0 ( 64-bit ), kindly advise :

Environment :
- AccessDatabaseEngine 2010 ( x64 ) + SP1
- Windows Server 2008 R2 Enterprise ( 64-bit )
- Microsoft SQL Server 2008 R2 Enterprise Edition (64-bit)
- IIS 7.5.7600
- Simple Excel_Template.xlsm with columns layout A-AM

Scripting ( with ASP.NET - C# ) to retrieve selected data from MSSQL and insert into the excel template file.

Everything working FINE if the dataset / number of rows less than 4170. 
The scripts started not behave correctly after 417X onwards :
- No exception when debugging
- OleDbCommand.ExecuteNonQuery successful ( return indicator 1 throughout entire loops )
- If put a break at 4170 row count then excel file .xlsm was generated successfully ( file size 615 KB )
- If put a break at 4180 row count then excel file .xlsm was generated but output file is blank ( exactly same as template file before insert any row/data )
- All processes, loops and connections are working fine.
- No data or formatting issue because the remaining rows having similar length and pattern

The scripts are similar to the below :

string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XXX.xlsm;Extended Properties=Excel 12.0 Xml;";

try
{
    using (OleDbConnection con = new OleDbConnection(connString))
    {
        con.Open();
        ...
        OleDbCommand cmd = new OleDbCommand(strSQL, con);
        cmd.ExecuteNonQuery();

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            ...
            cmd.CommandText = strSQL;
            cmd.ExecuteNonQuery();
        }
        con.Close();
    }
}
catch (Exception ex)
{
}

Did you ever solve this problem?, i have the same error, please comment.

Answer 3

I have same error. Is there any solution?

---

RG