CODEWITHSUNDEEP
sqlsql-servernullablesqlbulkcopy
Chad
Chad

Reputation: 912

How to use SqlBulkCopy with nullable columns

I’m having an issue using SqlBulkCopy when nullable columns are involved. It sounds like SqlBulkCopy doesn't know how to deal with nullable columns and throws an illegal size error when it encounters a zero length column. The error is "Received an invalid column length from the bcp client..."

I’m wondering what the best practice is for dealing with this. This seems to be a good forum post describing this issue and how to solve it for reading a csv file.

I think my situation is pretty A-typically and simple. I need to move a unknown amout of data from one database table to another database. The more simple answer for me would be to use SSIS/DTS or linked servers in sql server, however the customer wants the app to do the 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 );

Upvotes: 3

Views: 7889

Answers (3)

Dennis Coppens
Dennis Coppens

Reputation: 1

Checking on nullable values will solved alot. But I still had trouble with DateTime values, which can't be null in the database. My source is a CSV, so values can be null. Setting null values to DateTime.MinValue was not the solution, because of the difference of C# datetime min values and Sql min values.

By setting the destination columns to DateTime2 all problems with DateTime columns were solved. Other columns (int e.g.) can just be null without issues.

Upvotes: 0

Chad
Chad

Reputation: 912

The SqlBulkCopy class can't cope with null fields. It doesn't check if the field is null before trying to calculate it's length. Thus throwing an error.

The post i cited in the question references a implementation of IDataReader that throws DBNull.Value instead of just the null value -- this works around the defect with SqlBulkCopy.

Upvotes: 6

MkUltra
MkUltra

Reputation: 517

It should work if you have nullable checked in your destination field in question.

Upvotes: 0

Related Questions