Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Strange exception in SqlBulkCopy.WriteToServer RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi everyone

    I'm doing some data generation for testing and since I have to create a quite large set of data I'm using the SqlBulkCopy class. I create a DataTable in memory and load it with a batch of data to insert. I then use the SqlBulkCopy.WriteToServer to write the content of the DataTable to the server. The generated data inserted consists of a bigint(long) and an int column (which forms the composite primary key) and a number of columns with all null values. The basic loop code goes like this:

    1 int startID = 1;  
    2 int endID = 56700000;  
    3 int increments = 1000;  
    4 while (startID < endID && ContinueRunning)  
    5 {  
    6   int newEndID = startID + increments;  
    7  
    8   SqlBulkCopyOptions options = SqlBulkCopyOptions.Default;  
    9   SqlBulkCopy copy = new SqlBulkCopy(connectionString, options);  
    10   copy.DestinationTableName = "ScientificSamples";  
    11   DataTable table = _Generator.GenerateData(startID, newEndID);  
    12   table.TableName = "ScientificSamples";  
    13  
    14   copy.WriteToServer(table);  
    15   startID = newEndID;  
    16 }  
    17  

    Executing this code has some very strange behavior. At some point the WriteToServer throws an exception like:

    OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Stddev'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
       at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)
       at WFDBDataGenerator.DataInserter.StartInserting(String connectionString, LoggingCallback callback) in C:\projects\SiemensWFDB\src\WFDB\WFDBDataGenerator\DataInserter.cs:line 47

     
    Sometimes it throw after having inserted 1000 or 2000, other times it inserts 13000-14000 entries before breaking. It is not always the same column that is reported in the exception, but it is always one of the columns that has been created with a DBValue.Null in the DataTable.

    What really puzzles me here is the seemingly nondeterministic behavior: It's not always the same column, so why can I insert the same data some times but not other times? Why can I sometimes run to 13000 entries but other times break after just 1000 entries inserted?

    Any thoughts or ideas would be really appreciated...


    • Moved by nobugz Thursday, November 6, 2008 11:05 AM sql forums @ forums.microsoft.com [loc] (Moved from .NET Base Class Library to Off-Topic Posts (Do Not Post Here))
    Thursday, November 6, 2008 9:32 AM

Answers