SqlBulkCopy is giving me trouble when nullable columns are involved. It seems that SqlBulkCopy is unable to handle nullable columns and produces an invalid size error when it comes across a column with a zero length. It reads "BCP client reported an incorrect column length,".
What is the best course of action for handling this? A helpful forum post discussing this problem and how to fix it when reading a csv file is This.
My scenario seems to be very rare and straightforward. I have to transfer an unspecified amount of data from one database table to another. If the client wants the app to handle the data transfer, I would say that using SSIS/DTS or connected servers in SQL Server would be the most straightforward solution.
Is there a better steaming option for transporting data with nullable fields, or a recognized workaround for this?
//access db string src_db = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SourceDB.mdb;Jet OLEDB "; //sql db string dest_db = @"Data Source=.\TEST;Initial Catalog=testdb;User Id=user;Password=password;"; string sql = ""; OleDbConnection sdb = new OleDbConnection( src_db ); OleDbCommand cmd = new OleDbCommand( sql, sdb ); OleDbDataReader rs = null; SqlConnection db = new SqlConnection( dest_db ); SqlCommand clear = null; SqlBulkCopy bulk_load = null; // Read in the source table sql = "select * from someTable"; sdb.Open(); cmd = new OleDbCommand( sql, sdb ); rs = cmd.ExecuteReader(); // Import into the destination table bulk_load = new SqlBulkCopy( db ); bulk_load.DestinationTableName = "test"; bulk_load.WriteToServer( rs );
The SqlBulkCopy class cannot handle fields that are null. Before attempting to determine the field's length, it does not first check to see whether it is null. resulting in an error.
The article that I mentioned in the query makes reference to an IDataReader implementation that throws DBNull. This fixes the issue with SqlBulkCopy by using a value rather than merely the null value.
Verifying nullable values will solve several problems. Although DateTime values in the database cannot be null, I still encountered issues with them. The values in my source, a CSV, may be null. DateTime is being set to null values. Because the minimum values for C# datetime and SQL varied, MinValue was not the right answer.
All issues with DateTime columns were resolved by changing the target columns to DateTime2. It's okay for other columns (like integer ones) to merely be empty.