G
Guest
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.
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.