How to use SqlBulkCopy with null columns

I have a problem using SqlBulkCopy where null columns are involved. It looks like SqlBulkCopy doesn't know how to handle null columns and throws an illegal size error when a null-length column is encountered. Error "Received invalid column length from bcp client ..."

I am wondering what is the best way to solve this problem. This seems to be a good forum post describing the problem and its solution for reading the csv file.

I think my situation is pretty typical and simple. I need to transfer unknown data from one database table to another database. A simpler answer for me would be to use SSIS / DTS or linked servers on sql server, however the client wants the application to do data movement.

Is there a known work around for this, or a better steaming solution for moving data with nullable fields?

//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 );

      

+2


source to share


2 answers


The SqlBulkCopy class cannot handle null fields. It does not check if the field is null before trying to calculate its length. So an error is thrown.



The post I quoted in the question refers to an implementation of IDataReader that produces DBNull.Value instead of null - this works around a defect with SqlBulkCopy.

+5


source


It should work if you have NULL checked in the appropriate box.



0


source







All Articles