Getting a constraints exception when loading a DataReader in a DataTable

Question

I run a Merge query against a SQL2008 db that returns the output from the merge using the following c# code:

cmd.CommandText = query;
if (conn.DBConn.State == ConnectionState.Closed) conn.DBConn.Open();
DbDataReader dbReader = cmd.ExecuteReader();
DataTable dt = new DataTable("Results");
dt.Load(dbReader);

The last line throws an error:

System.Data.ConstraintException - Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

I found this on MSDN, and it fits my scenario, but how do I actually fix this?

Clearing the primary key with dt.PrimaryKey=null; does not work

The code above will be used for many tables.

Answer 1

violating non-null, unique, or foreign-key constraints.

non-null: check if some of the field(s) returning null and if null is allowed for the field.

unique: seems to be confirmed

Answer 2

Maybe your query is returning multiple result sets? Run your query in Management Studio or similar to see.

Answer 3

Put dt.BeginLoadData() before the call to Load.

 dt.BeginLoadData();
 dt.Load(dbReader);
 dt.EndLoadData();

Answer 4

The simplest workaround I've found for this error is to merely wrap it in a Catch block and ignore the error. I discovered it while implementing cjb110's excellent article on digging up more information about the constraint errors. To my surprise, I learned by sheer accident that we can ignore the error altogether and use the datatable as-is. I've verified that the VB code below (feel free to run it through a code converter for C# syntax) returns all of the rows and columns as expected, at least in the instances I've encountered the problem (which in my case have been mainly on ADOMD Command objects).

 Try

 TempDataTable.Load(PrimaryCommand.ExecuteReader)

Catch ex As ConstraintException

  End Try

Note that this question is a probable duplicate of a newer thread DataTable.Load, One or more rows contain values violating non-null

Answer 5

Do this. Put the select output as a Derived table and select * from the derived.

It seems to help.

like

Select * from
(Select columnB,columnB ) -- if your table is being created on the fly.