Default DataTable Constraints with SQL Server 2000 vs 2005

Hi,

We are in the process of upgrading from SQL Server 2000 to 2005. We have
one stored procedure that returns the result of a join on two tables, table A
and B. Part of the result set is the primary key for Table A. Because of
the nature of the join, data in table B can result in multiple rows with the
same information from table A. The rows are only different in the
information from table B. Thus, the primary key from table A can appear in
two rows.

In our C# code, we populate a DataSet/DataTable with the result of this
query. Using SQL 2000 it ran fine. As soon as we moved to 2005, we now get
the following error when trying to populate the DataSet:

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

An analysis of the DataSet in the debugger reveals that, when running
against SQL 2005, the DataSet is automatically receiving a PrimaryKey
Constraint object. When running against SQL 2000, there are no contstraints.
Since the primary key from Table A appears multiple times in the result set,
I'm assuming it is this default constraint that is being violated.

This is obviously different behavior between SQL Server 2000 and 2005, so,
how can I turn this off for SQL 2005, either in the connection string, or
somewhere else? I know I can just set the EnforceConstraints property to
false, but the code that runs the query is part of our generic DataAccess
layer so it is not specific to just this query.

Thanks.

Jeff,

In fact are you misusing a DataSet as a kind of List class. However I cannot
say that I don't do that often although I use than a DataTable. But if this
list class is a part of your business environment, I would create a clear
(generic) list from it using the DataReader.

Cor

It's understandably not the best way to go about this. This code was already
in place when it came to me. It's rather not have to change it since it is
part of our business environment and used everywhere. Isn't there a way to
turn off this behavior in SQL 2005?

"Cor Ligthert[MVP]" wrote:

> Jeff,
>
> In fact are you misusing a DataSet as a kind of List class. However I cannot
> say that I don't do that often although I use than a DataTable. But if this
> list class is a part of your business environment, I would create a clear
> (generic) list from it using the DataReader.
>
> Cor
>

I found that it is the following line of code that is causing the default
constraint to be created:

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

da is the SQLDataAdapter for the DataSet. This code existed when we were
using SQL 2000, so for some reason, when using SQL2005, the default
constraint is being created where it wasn't with SQL 2000. Also if I use
MissingSchemaAction.Add, it works fine with 2005.

What changed in SQL 2005 that causes this new behavior? Anyone know?

Jeff

"Jeff" wrote:

> It's understandably not the best way to go about this. This code was already
> in place when it came to me. It's rather not have to change it since it is
> part of our business environment and used everywhere. Isn't there a way to
> turn off this behavior in SQL 2005?
>
> "Cor Ligthert[MVP]" wrote:
>
> > Jeff,
> >
> > In fact are you misusing a DataSet as a kind of List class. However I cannot
> > say that I don't do that often although I use than a DataTable. But if this
> > list class is a part of your business environment, I would create a clear
> > (generic) list from it using the DataReader.
> >
> > Cor
> >

Jeff,

Thanks for this information, I did not know this.

Cor