ASP.NET SqlBulkCopy does not insert all records on server

.net asp.net database sql sqlbulkcopy

Question

Why SqlBulkCopy does not insert all of the reader's records is a mystery to me. 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();
   }              

About 300k records need to be mass copied. When I run this from localhost to the same database, it works, but when I run it from the server, it always inserts around 7k entries without throwing an exception or error. There are 4GB of free RAM on the server. 1,5GB on localhost.

Why it works well on localhost but not on the server is beyond my comprehension.

1
2
12/11/2013 2:20:23 PM

Popular Answer

I had the similar issue and found the solution by include the next in 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';";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

On my localhost everything was running well, but not on the server.

Try it:)

Notes:

Column names rather than data are found in the first row, as indicated by "HDR=Yes;". The reverse is indicated by "HDR=No;".

It is safer to always get data for mixed data columns with IMEX=1. Consider the possibility that one Excel file may function well because the data in that file leads the driver to infer one data type, while the data in another file leads the driver to infer a different data type. Your app may crash as a result of this.

0
5/23/2016 6:46:04 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow