Record Count randomly 0 instead of 1

[martin]

We have the problem, that a very simple point query ("select blobfield from table where id = 12345") does fail once in a while, say every other day. When Inserting a row then instead of updating, the server returns a key violation.

It returns 0 as record count altough a record with that key is existing in the table. After destroying and recreating the query object, the problem disappears, the query correctly returns 1 row.

Is this some kind of caching problem ? Or a bug in SQL Server 2008 Enterprise ?

We cannot provide you with a small sample because the table has 15 Mio rows.

Any ideas what we could do ?

Regards
Martin

[Dimon]

When Inserting a row then instead of updating, the server returns a key violation.

Please make sure that the table has autoincrement (IDENTITY) field.

It returns 0 as record count altough a record with that key is existing in the table. After destroying and recreating the query object, the problem disappears, the query correctly returns 1 row.

Please, specify exact steps to reproduce the problem.

[martin]

When Inserting a row then instead of updating, the server returns a key violation.

Please make sure that the table has autoincrement (IDENTITY) field.

Why would we need an identitiy field ?

We want to INSERT a new row when the data for a specific primary key is not yet there but UPDATE it with new data, when some of the data for a specific primary key is already there.

We have the PROBLEM, that a simple SELECT * FROM TABLE WHERE KEY = XY returns NO ROW from time to time, altough you can see in SQL Management Studio, that there is actually a row for this query. After destroying and recreating the query object, the very same SELECT correctly returns a row without any change on the database.

Sorry, but this is really driving us mad. When we cannot assume that a query always returns a row when there actually is a row in the database, this is a KILLER BUG !!!

Again, I don't say this is a bug in your driver, but there is a bug and I would be very happy if you could help us to locate where the problem is asap.

Please, specify exact steps to reproduce the problem.

As I said, unfortunately we cannot provide simple step-by-step instructions. The database is huge and so is the incoming traffic. Problem only appears from time to time. We really need another way to find the bug. Do you have some tools or a special build we could use to find the problem ?

[Dimon]

Try to use standard dbExpress drivers supplied by Borland and check if the problem still persist.

[martin]

Are these drivers supplied with Delphi 6 ? Can I use them together with TQRSQLConnection ?

[Dimon]

Unfortunately, support of standard dbExpress driver to connect to SQL Server begins from RAD Studio 2007.
But to help you, we need more information about this problem.

[martin]

We can close this issue

Client Operator has setup two loader apps to populate the same server instead of two different servers so simultanous inserts have been performed which caused the primary key violations

So no bug in the driver, of course

Thanks for support.