Mysterious constraints problem with SQL Server 2000
- by Ramon
Hi all
I'm getting the following error from a VB NET web application written in VS 2003, on framework 1.1. The web app is running on Windows Server 2000, IIS 5, and is reading from a SQL server 2000 database running on the same machine.
System.Data.ConstraintException:
Failed to enable constraints. One or
more rows contain values violating
non-null, unique, or foreign-key
constraints. at
System.Data.DataSet.FailedEnableConstraints()
at
System.Data.DataSet.EnableConstraints()
at
System.Data.DataSet.set_EnforceConstraints(Boolean
value) at
System.Data.DataTable.EndLoadData()
at
System.Data.Common.DbDataAdapter.FillFromReader(Object
data, String srcTable, IDataReader
dataReader, Int32 startRecord, Int32
maxRecords, DataColumn
parentChapterColumn, Object
parentChapterValue) at
System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, String srcTable, IDataReader
dataReader, Int32 startRecord, Int32
maxRecords) at
System.Data.Common.DbDataAdapter.FillFromCommand(Object
data, Int32 startRecord, Int32
maxRecords, String srcTable,
IDbCommand command, CommandBehavior
behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, Int32 startRecord, Int32
maxRecords, String srcTable,
IDbCommand command, CommandBehavior
behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet)
The problem appears when the web app is under a high load. The system runs fine when volume is low, but when the number of requests becomes high, the system starts rejecting incoming requests with the above exception message. Once the problem appears, very few requests actually make it through and get processed normally, about 2 in every 30. The vast majority of requests fail, until a SQL Server restart or IIS reset is performed. The system then start processing requests normally, and after some time it starts throwing the same error.
The error occurs when a data adapter runs the Fill() method against a SELECT statement, to populate a strongly-typed dataset. It appears that the dataset does not like the data it is given and throws this exception. This error occurs on various SELECT statements, acting on different tables.
I have regenerated the dataset and checked the relevant constraints, as well as the table from which the data is read. Both the dataset definition and the data in the table are fine.
Admittedly, the hardware running both the web app and SQL Server 2000 is seriously outdated, considering the numbers of incoming requests it currently receives. The amount of RAM consumed by SQL Server is dynamically allocated, and at peak times SQL Server can consume up to 2.8 GB out of a total of 3.5 GB on the server.
At first I suspected some sort of index or database corruption, but after running DBCC CHECKDB, no errors were found in the database. So now I'm wondering whether this error is a result of the hardware limitations of the system. Is it possible for SQL Server to somehow mess up the data it's supposed to pass to the dataset, resulting in constraint violation due to, say, data type/length mismatch?
I tried accessing the RowError messages of the data rows in the retrieved dataset tables but I kept getting empty strings. I know that HasErrors = true for the datatables in question. I have not set the EnableConstraints = false, and I don't want to do that.
Thanks in advance.
Ray
