x

Data field not updated Occasionally on one machine

Hi everybody,

I am having a rather peculiar problem and hope you can shed some light on it.

This is an in-house developed solution and has been running for more than 10 years. Recently I found that there is one user (or maybe one machine) having the problem of updating a particular column every now and then.

I've done some investigation and following is my finds: 1) when the problem occurred (ie, the change to this particular field was not saved) the changes to other fields of the record were saved.
2) the subsequent tries normally work. 3) the error ratio is about 1/5.

This is a frequently used table accessed by multiple users at any given time. There has never been such problem reported in other data columns of the table. The fact that the same code works fine on other computers (and, it happens on this computer occasionally) indicates the data saving logic is correct. It looks like a machine related issue, but, other aspects of the WS (other applications, and other functions of the same application) works fine...I have run out of ideas on how to troubleshoot this problem!

Thank you all in advance for your help!

more ▼

asked Nov 11 at 12:08 AM in Default

avatar image

Yvonnez
52 1 2 6

Is there any code that you can post so that we can see how the column is supposed to be updated? Can someone update the field from behind the scenes outside of the normal process? Can you repeat the problem?

Nov 12 at 07:38 PM JohnM

Thank you for the response, John! Here are the answers to your questions:

1) the code that updates the column: UPDATE SET BillToAddr1 = '', BillToAddr2 = '1321 Ketch Court', BillToCity = 'Coquitlam', BillToCountry = 'CAN', BillingNote = 'Testing 101' (this is the field in question), ModifiedBy = 'YvonneZ', ModifiedDate = '11/06/2017', ModifiedTime = '13:32:07' .WHERE SONum = '175337' I've observed a couple of times when the error happened (the BillingNote field was not updated) the other fields, such as ModifiedBy, ModifiedDate were updated.

2) Can someone update the field from behind the scenes outside of the normal process? No. If others want to update the field they will have to use the same procedure.

3) Can you repeat the problem? It happens randomly but I was able to repeat the problem, which is how I came up the ratio of error is about 1/5. When it happened I queried the table and the field (BillingNote) was empty. But, this error only happens (for what we know) in this machine. The other users do not have such problem and I cannot repeat the problem in my test/dev environment.

Again, thank you for your time and your brain-power!

Yvonne

Nov 14 at 06:25 PM Yvonnez

Are there any triggers on the table?

Is there anything else that updates that table?

Nov 14 at 10:57 PM ThomasRushton ♦♦

Thanks for the questions, Thomas! No, there is no any triggers on that table. This is a rather old-fashioned application in which most of the data process logics are done in the code rather than in the DB.

There is an application level lock on the table allowing only one user to update/Read the table at a time. So, if somebody else updates the table, it has to be after this user releases the lock.

Thanks again!

Yvonne

Nov 14 at 11:11 PM Yvonnez
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2

asked: Nov 11 at 12:08 AM

Seen: 23 times

Last Updated: Nov 14 at 11:11 PM

Copyright 2017 Redgate Software. Privacy Policy