asked
Strange SQL Partial Update issue
I have a strange question...only because I have never seen this behavior before. I'm trying to debug a very rare, intermittent bug in an application (.NET/SQL Server 2008 R2) that I support. First I need to give you some background...the application updates 2 fields in a table using a SQL statement like this: Update table set col1 = 'valueA', col2 = 'valueB' where col3 = 'valueC' and col4 = 'valueD' 99.9% of the time it works fine...but every so often only **column 1** gets the updated value - ***column 2 doesn't!!*** I've never seen this happen before - a case where one field in a single statement succeeds and one field fails...is this even possible? Without SQL Server generating an error? I keep looking for some other code that could be the culprit but this statement is the only candidate...it's just that I've never seen this behavior before. Any input would be appreciated. NOTE: I inherited this application and I already know that it's bad mojo to use sql directly in an application. All new sql gets put into views and SProcs...but this is legacy code and I don't have time to rewrite the entire application. I'm just interested in the behavior of one field getting updated and the other one failing. Thanks, George
It isn't possible for one column to get updated and one column not...not if it is in the same update statement and there are no triggers etc. on the table. Could it be that the update statement fails, but the error isn't caught and raised by your application? You could also have concurrency issues...i.e. 2 users executing the same update statement on the same record in short succession (last update wins). The best way to find out in my opinion is to run a trace so that you can see exactly what is being executed on the server.
Great set of suggestions, so I moved them from a comment to an answer.
+1 yep, if there's nothing obvious code-wise, get a trace going.
