ASP.NET SqlBulkCopy does not insert all records on server

.net asp.net database sql sqlbulkcopy

Question

I don't have any idea why SqlBulkCopy does not insert all of the records from the reader. Code:

using (OleDbDataReader dr1 = cmd.ExecuteReader())
   {
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ss, SqlBulkCopyOptions.TableLock, null))
       {
            bulkCopy.BulkCopyTimeout = 0;
            bulkCopy.BatchSize = 500;
            bulkCopy.DestinationTableName = tableName;
            try
            {
                bulkCopy.WriteToServer(dr1);
            }
            catch (Exception ex)
            {
                Log.LogSystemLog(ex.ToString());
                return false;
            }

       }

       GC.Collect();
       dr1.Close();
   }              

I want to bulkCopy about 300k records. It works when I'm running this from localhost to the same database, but when I run this from the server - it always inserts ~7k records... and no error, or exception. On the server 4GB free RAM are available. On localhost ~ 1,5GB.

I have no idea why it's working properly on localhost, but on the server it is not.

Popular Answer

I had the same problem, solved by adding the following to my connection string.

IMEX=1

Example:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath(rutacom) + ";" + "Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";

In my localhost was working perfectly, but not on server.

Try it :)

Notes:

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why