October 12, 2011 at 1:49 pm
Something very strange happened yesterday that I have never seen before. We have a very simple update statement that updates 1 column -- PodStartDateTime -- in table ProdOrdDtl:
UPDATEProdOrdDtl
SETPodStartDateTime= @StartDateTime
WHEREPodKey = @PodKeyAND
PodStartDateTimeIS NULL
We also have a trigger that writes all data changes to an Audit table.
AuditId TableNameRowIdOperationOccurredAtPerformedByFieldNameOldValueNewValue
169117438ProdOrdDtl1504872 UPDATE2011-10-11 10:22:03.773mmerritt PodCalcOutputQty0.00000
169117439ProdOrdDtl1504872 UPDATE2011-10-11 10:22:03.773mmerritt PodInputUOM TN GA
169117437ProdOrdDtl1504872 UPDATE2011-10-11 10:22:03.773mmerritt PodStartDateTime10/11/2011 10:22:03 AM
The Audit shows that not only did the PodStartDateTime column get updated (correctly) but at the exact same time, the PodCalOutputQty was updated from null to 0.00000 and PodInputUOM was changed from TN to GA. Has anyone ever seen anything like this happen before? The stored procedure that contains the update statement is accessed by a wireless scanning device (bar code reader). The application has been running for 10 months and this is the first time anything like this has happened. Any clues where I should start to look for a cause?
Thanks.
October 12, 2011 at 2:09 pm
Is this the only known occurrence of this? How robust is your audit? I would say that it was not the stored procedure that did this, but something else that was executed at the same time. The only other thing I can think of is a trigger. One more question... Is this a table or are you updating through a view?
Thanks,
Jared
Jared
CE - Microsoft
October 12, 2011 at 2:11 pm
Other thoughts... Have any changes been made to your audit table? Do any of the columns in the audit table have defaults? Are any of the columns set to NOT NULL?
Thanks,
Jared
Jared
CE - Microsoft
October 12, 2011 at 3:50 pm
This is the first time I’ve seen anything like this.
The audit has been in place since the beginning of 2009 and has been very solid.
I’ve checked all code that can possibly update all 3 of these columns at the same time and there is only 1 stored procedure that can do it, and the bar code scanning device application does not call that stored procedure. The user swears she did not access the desktop form that does access that stored procedure and I confirmed that to be the case because if she had, another column with the ‘change date’ would have been updated, and it wasn’t.
I confirmed that there is no trigger other than the Audit trigger on the ProdOrdDtl table.
This is a table. I am not updating through a view.
There are no default values on the Audit table, and the only columns that allow nulls are FieldName, OldValue, and NewValue.
Several weeks ago, we had an unusual incident where a stored procedure kept returning an error to the user, and all I did was re-compile it and the problem resolved. I could not explain it other than it seemed that somehow the procedure had been corrupted. I can't check to see if something like that is going on with this stored procedure because in my efforts to troubleshoot the problem, I've recompiled the stored procedure this morning. Is the corrupted stored procedure scenario plausible? If so, what could cause it?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply