Data Shifting Between Records

Is there a problem with stability when one uses too many text (memo) fields?
I'm having a problem with data from one record occasionally ending up in
another record, though apparently not through any user interaction.

I'm using SQL 7 and accessing the tables through the ODBC driver. There are
two tables with a one-to-one relationship -- TableA and TableB. TableB is
the one that's having this occasional problem. TableB has about 30 text
fields out of a total of about 50 fields in the table. The problem is
usually with one or two text fields containing data from a different record,
one that was created close to when the problem record was created.

Example: TableB has fields 1-9, say. In one record, field 1 has A, field 2
as B, field 3 has C, and so on.

In another record (created shortly after the other one), field 1 has AA,
field 2 has BB, field 3 has CC, and so on.

The user works with the records, everything's fine. Then one day the user
notices that in the second record, field 1 has AA, field 2 has BB, but field
3 has C instead of CC. In other words, all data's fine, except for one,
maybe two fields, that have data from a previously-created record.

At first glance this seems to be a user-interaction thing, that somehow the
user inadvertantly placed data from the older record into the newer one,
either through a shortcut, or by having that data on the clipboard, or
whatever. But a recent incident opposes that theory.

I have two forms in the front end for editing records (the forms are bound
to the ODBC table links). Form1 is bound only to TableA (the one that
doesn't have the problem); Form2 is bound to a query that is TableA joined
with TableB.

In the recent incident where data shifted, both the record that was affected
and the record from which the data came were both only edited with Form1. In
other words, TableB never came into play; yet its data was somehow affected.

When a record is created, the user completes a few fields in a form, and
then a stored procedure creates a record in TableA and then a sister record
in TableB (using the TableA record's autonumber primary key as its primary
key). A couple of user entered values are entered into the TableB record.
But if the user is using Form1, they never see the TableB record.

In this case, the TableB record's two fields got changed to fields from an
earlier record (one which was created a little earlier the same day), even
though both records were only edited in Form1 (according to the history
log), which doesn't touch TableB.

Thus, I'm wondering if there's a possibility that either the SQL database or
the ODBC driver somehow shifted the data from one record into another. That
seems far-fetched. But, at this point, since a table that the user didn't
touch somehow had its data changed to data from a different record, I'm
trying to explore all possibilities.

Thanks for any insight!

Neil

re: Data Shifting Between Records

"Neil" <nospam@nospam.netwrote in message
news:7uhFj.8045$Rq1.6717@nlpi068.nbdc.sbc.com...<snipped>

Can you post the code and DDL for the tables and calls being made?

That might help.

If this were a real bug, I suspect it would be reported by now. (and maybe
it has, have you tried the MS knowledge base?)



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

re: Data Shifting Between Records

Neil (nospam@nospam.net) writes:Since your description is very abstract, it's difficult to tell what
is going on, but the likelyhood that the ODBC driver would confuse tables
on its own is nil. The likelyhood that SQL Server would do it also very
small, but it could be a corruption issue. However, a corruption usually
manifests itself with violent error messages. But run DBCC CHECKDB on the
database to rule that out.

I would direct my attention to the application code. That is, the stored
procedures and the client code. Something may not be what you think it is.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: Data Shifting Between Records

Since your description is very abstract, it's difficult to tell whatA point of clarity: it's not confusing tables, but confusing records. The
correct tables are being updated. But, somehow, a related table had its data
modified in the corresponding record.

I mention the ODBC driver, especially in the context of multiple text
fields, because it has been known to go to the wrong record when going to a
record in a large recordset. In one application, I frequently would have the
ODBC driver go to the record that was exactly 100 in sequence behind the one
it was supposed to go to. I found that moving to the last record before
going to the one I needed resolved the issue. I think it was fixed with a
later patch, though.

Come to think of it, I don't think that was a problem with the ODBC driver,
but with the Jet engine, which was using the ODBC driver to access cs
databases. So it could be a Jet issue, actually.

Point is, a front end engine getting confused when accessing a record in a
cs database via ODBC is not without precedent.
CHECKDB said everything was OK. Didn't think it was SQL Server, but figured
it wouldn't hurt to ask here, especially since the people here might have
come across something like this while using ODBC in some applications.
Yes, that would be the likely culprit. Only thing is, there is no code that
updates those fields. And it's usually just one or two fields, not the
entire record, that's awry. Could be something the user's doing; but I can't
imagine what. They say they enter data in the record, check it, everything's
fine. Then, sometime later, they go back and look at the record, and it has
data from another record in one or two fields. It's happening about once or
twice a month now.

Thanks,

Neil

re: Data Shifting Between Records

Neil (nospam@nospam.net) writes:Yes, Access or Jet or something else that uses the ODBC driver could
maybe do something like that. But the driver on its own? That seems less
likely to me.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx