Duplicated data - how to understand it please?

7 Jul 2009 7:47 AM

Edward

I've recently taken over support and maintenance of a system that was
developed and delivered by another company. It's a VS2005 Windows
application written in VB sitting on a SQL Server 2000 database. I
imagine (though I haven't seen any documentation) that it underwent
reasonably rigorous testing. The system is for a company making
medical supplies, and it stores information about clinical trials. So
the data has be pretty reliable, yes?

So here's the thing. The hierarchy of the data is broadly Clinical
Studies 1..n Patients 1..n Operations 1..n Operation Elements.

What appears to have happened is that in a couple of instances there
have been duplicate elements entered into one of the Operation
Elements table. The only place in the code (that I've been able to
find) where that table is written to is a form with a Save button. The
data is based on XSD files, and makes use of built-in CRUD functions.
According to what I've been able to observe when the user presses the
"Save" button there is either an existing record or one needs to be
created:

            If Me.Mode = enums.FormMode.Add Then
                Return CreateMultiOptionDataRows()
            Else
                Return UpdateExistingMultiOptionDataRows()
            End If

The system contains extensive audit history tables which are populated
by Insert, Update and Delete triggers on the production tables, so I
have been able to recreate the exact history of one of these non-
conforming records, but when I do that it doesn't create these
duplicated sub-records.

So, my rather vague question is: given that I can't reliably
reproduce the problem, but my client is unwilling for obvious reasons
to put it down to a "glitch", where should I look for the answer. My
gut feeling is that it is likely to be an improperly rolled-back
transaction, but surely the built-in data objects in VS2005 (such as
XSD) wouldn't fail like that. Would they?

Thanks

Edward

7 Jul 2009 7:32 PM

Andy

What does the error message say? If its a duplicate key problem, the
message will tell you.

You can review SQL Server logs located in the SQL Server installation
under Program Files/Microsoft SQL Server/MMSQL.4/MSSQL/LOG

This folder will contain generations of error logs, and if you have a
duplicate key error it will be reported in one of them. ERRORLOG is
the current log.