2

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.

callisto
  • 4,921
  • 11
  • 51
  • 92

5 Answers5

3

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

SQLServerSteve
  • 332
  • 1
  • 11
  • 22
1

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.
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
F Ahmad
  • 11
  • 1
0

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

 dt.BeginLoadData();
 dt.Load(dbReader);
 dt.EndLoadData();
d.popov
  • 4,175
  • 1
  • 36
  • 47
  • I was hoping this would work for me since it "Turns off notifications, index maintenance, and constraints while loading data," according to https://msdn.microsoft.com/en-us/library/system.data.datatable.beginloaddata(v=vs.110).aspx . It didn't have any effect though. – SQLServerSteve Jun 13 '17 at 23:46
  • @SQLServerSteve any success with this? You had the same issue, and the other answers also didn't help? – d.popov Jun 21 '17 at 15:19
  • @dpopov - See my answer above, where I simply caught the error and ignored it, which did the trick. I just left this comment on your answer to let you know your approach didn't work in my case, for some reason (I tried yours first). – SQLServerSteve Jun 22 '17 at 04:16
0

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

Duncan Smart
  • 31,172
  • 10
  • 68
  • 70
0

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

Rudolf
  • 1
  • The returned table is not based on a strongly typed dataset. It is a table that is generated by the SQL engine with different columns according to the tables I merged. There is NO KEY defined for the returned table! The interesting thing is that the MERGE is completed successfully, so it is only the output from that MERGE that causes an issue. – callisto Nov 02 '11 at 13:08