Mysterious constraints problem with SQL Server 2000

Question

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

Answer 1

The problem is the lack of a useful diagnostic error message. I've written a class that contains methods to tell you the problem with your constraints instead of just giving you a useless generic error message. You use the methods in the following fashion.

Dim ds As New dsMyDataset

'Turn off constraints during the data load'
DatasetAnalyzer.DatasetAnalyzer_Init(ds)

'Ready to fill one or more tables'
Dim da1 As New dsMyDatasetTableAdapters.Table1TableAdapter
da1.Fill(ds.Table1)

'Checks relationships and constraints before turning them back on.'
DatasetAnalyzer.DatasetAnalyzer_AnalyzeAndConfirm(ds)

'Ready to use your dataset now'
Return ds

Here is the complete class.

Public Class DatasetAnalyzer

#Region " DatasetAnalyzer "

    'Run this before loading tables in your dataset, if you plan to call DatasetAnalyzer_GetMissingParentRows.
    'Must call DatasetAnalyzer_AnalyzeAndConfirm when done creating dataset to re-enable relations.
#Region " DatasetAnalyzer_Init "
    Public Shared Sub DatasetAnalyzer_Init(ByVal ds As Data.DataSet)
        ds.EnforceConstraints = False

        ds.BeginInit()
        For Each dt As Data.DataTable In ds.Tables
            dt.BeginInit()
            dt.BeginLoadData()

        Next

    End Sub
#End Region

    'Checks for dataset constraint errors and gives detailed error messages if any are found.
    'Assumes DatasetAnalyzer_Init is called on that dataset
#Region " DatasetAnalyzer_AnalyzeAndConfirm "
    Public Shared Sub DatasetAnalyzer_AnalyzeAndConfirm(ByVal ds As Data.DataSet)
        DatasetAnalyzer_EnsureInitialization(ds)

        Try
            For Each dt As Data.DataTable In ds.Tables
                dt.EndLoadData()
                dt.EndInit()
            Next
            ds.EndInit()

            ds.EnforceConstraints = True

        Catch ex As Data.ConstraintException
            'We've found a constraint exception...figure out what it is

            Dim sErrorMessage As String = "DatasetAnalyzer_AnalyzeAndConfirm : "

            For Each oTbl As Data.DataTable In ds.Tables
                If oTbl.HasErrors Then

                    'Report the first error only
                    Dim oRow As Data.DataRow = oTbl.GetErrors(0)
                    sErrorMessage &= oTbl.TableName & " : " & oRow.RowError & " : "

                    'Detail for Foreign Key Violations
                    If oTbl.ParentRelations IsNot Nothing Then
                        For Each oRel As Data.DataRelation In oTbl.ParentRelations
                            If oRel.ChildKeyConstraint IsNot Nothing AndAlso oRow.GetParentRow(oRel) Is Nothing Then
                                'a parent constraint for this relation exists and the data that is constrained is non-existant.  If this isn't a null value then we found a problem
                                For Each o As Data.DataColumn In oRel.ChildColumns
                                    If Not oRow.IsNull(o) Then
                                        'We have a confirmed foreign key violation...generate a pretty message

                                        Dim ParentColumnNames(oRel.ParentColumns.Length - 1) As String
                                        Dim ChildColumnNames(oRel.ChildColumns.Length - 1) As String


                                        For i As Int32 = 0 To ParentColumnNames.Length - 1
                                            ParentColumnNames(i) = oRel.ParentColumns(i).ColumnName
                                        Next

                                        For i As Int32 = 0 To ChildColumnNames.Length - 1
                                            ChildColumnNames(i) = oRel.ChildColumns(i).ColumnName
                                        Next

                                        sErrorMessage &= "ParentTable = " & oRel.ParentTable.TableName & " (" & String.Join(", ", ParentColumnNames) & "), "
                                        sErrorMessage &= "ChildTable = " & oRel.ChildTable.TableName & " (" & String.Join(", ", ChildColumnNames) & "), "

                                    End If
                                Next
                            End If
                        Next
                    End If


                    'Additional Column info that might be usefull
                    If oRow.RowError.Contains("MaxLength") Then
                        For Each oCol As Data.DataColumn In oRow.GetColumnsInError
                            sErrorMessage &= oCol.ColumnName & ".MaxLength = " & oCol.MaxLength
                        Next
                    End If


                    'Report the error with details about the row that errored
                    sErrorMessage &= Environment.NewLine & Environment.NewLine & "Debug Data = " & DatasetAnalyzer_GetRowDebugData(oRow)
                    Throw New Exception(sErrorMessage)

                End If
            Next


            Throw New Exception("Dear Developer, Unknown Constraint Exeption", ex)
        End Try

    End Sub
#End Region

    'Returns an array of detached rows that are "missing" in the ParentTable.
    'Returns an empty array if no values exist
#Region " DatasetAnalyzer_GetMissingParentRows "
    Public Shared Function DatasetAnalyzer_GetMissingParentRows(ByVal ParentTable As Data.DataTable) As Data.DataRow()
        If ParentTable.DataSet Is Nothing Then
            Throw New Exception("Dear Developer, DatasetAnalyzer_GetMissingParentRows : ParentTable must belong to a dataset.  Table = " & ParentTable.TableName)
        End If

        DatasetAnalyzer_EnsureInitialization(ParentTable.DataSet)

        Dim drMissingParents As New Collections.Generic.List(Of Data.DataRow)

        Try
            'Turn on the constraints to see if anything breaks
            ParentTable.DataSet.EnforceConstraints = True

        Catch ex As Data.ConstraintException

            For Each oRel As Data.DataRelation In ParentTable.ChildRelations
                If oRel.ChildKeyConstraint IsNot Nothing AndAlso oRel.ChildTable.HasErrors Then
                    'This relationship has a child key constraint...this child table has errors

                    For Each oRow As Data.DataRow In oRel.ChildTable.GetErrors
                        If oRow.GetParentRow(oRel) Is Nothing Then
                            ' This foreign key that is constrained is non-existant.  If this isn't a null value then we found a problem
                            For Each o As Data.DataColumn In oRel.ChildColumns
                                If Not oRow.IsNull(o) Then
                                    ' non-null missing foreign key constraint
                                    Dim drMissingParent As Data.DataRow = ParentTable.NewRow

                                    ' Create the proposed parent record by matching the child record
                                    For i As Int32 = 0 To oRel.ParentColumns.Length - 1
                                        drMissingParent(oRel.ParentColumns(i)) = oRow(oRel.ChildColumns(i))
                                    Next

                                    'Search for a duplicate Missing Parent...only need to report each one once
                                    Dim bFoundDupe As Boolean = False
                                    For Each dr As Data.DataRow In drMissingParents
                                        bFoundDupe = True

                                        For i As Int32 = 0 To ParentTable.Columns.Count - 1
                                            If Not dr(i).Equals(drMissingParent(i)) Then
                                                bFoundDupe = False
                                                Exit For
                                            End If
                                        Next

                                        If bFoundDupe Then Exit For
                                    Next

                                    If Not bFoundDupe Then
                                        drMissingParents.Add(drMissingParent)
                                    End If

                                    Exit For 'Checking for non-nulls Columns
                                End If
                            Next
                        End If
                    Next
                End If
            Next
        End Try

        ParentTable.DataSet.EnforceConstraints = False

        Return drMissingParents.ToArray
    End Function
#End Region

    'Returns the string representation of row data
#Region " DatasetAnalyzer Private Support Methods "

    Private Shared Function DatasetAnalyzer_GetRowDebugData(ByVal oRow As Data.DataRow) As String
        Dim Values(oRow.Table.Columns.Count - 1) As String


        For Each oCol As Data.DataColumn In oRow.Table.Columns
            Dim Value As String
            If oRow.IsNull(oCol) Then
                Value = "<NULL>"
            Else
                Value = oRow(oCol).ToString
            End If

            Values(oCol.Ordinal) = oCol.ColumnName & ":" & Value
        Next


        Return String.Join(", ", Values)
    End Function

    Private Shared Sub DatasetAnalyzer_EnsureInitialization(ByVal ds As Data.DataSet)
        If ds Is Nothing Then
            Throw New Exception("Dear Developer, Must construct the ds object before calling InDatasetAnalyzer_Init (ds = New ...)")
        End If

        If ds.EnforceConstraints Then
            Throw New Exception("Dear Developer, call DatasetAnalyzer_Init before calling DatasetAnalyzer_AnalyzeAndConfirm")
        End If
    End Sub
#End Region
#End Region

End Class

Now you should be able to troubleshoot with useful information.