SQL Server Performance Forum – Threads Archive

Constraint Violations on tables w default value

Greetings, We just upgraded a development test server to SQL Server 2000 and then applied SP3. Following the upgrade we have experienced problems with updating tables that have default values (in all cases getdate()) associated with them. The error that comes up is "Row cannot be located for updating. Some values may have changed since it was last read.". Interestingly the problem does not occur if you fire the update directly from Query Analyzer just when you run the update through Enterprise Manager or through web application. Because of the scenario listed above I immediately thought MDAC but a reinstall of SP3 did not fix it (did not run a MDAC install seperate, should try this). I found article 300586 on the MS site which describes the error perfectly however they do not reference SQL Server. Curious if anyone has seen this and if so, what did you do to work around it. Let me know if you can provide any further direction or ideas. TIA David
David

Do you have a primary key or unique index in the table? And if so are you using these to identify the row to update. If the row you are updating trough EM or the web application (if you use a cursor) doesn’t have this and there are other identical rows (same date etc.) then it can’t locate the row to update. /Argyle

[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]Well, I thought that this was a problem associated with the upgrade however, the problem is actually with the type of connection string used by the code that worked fine in the previous version of SQL Server but not in 2000. Additionally, I had two problems that were so similar in nature that they appeared to be the same thing and I was misled (by my own foolishness) to combine them into one because we had just completed the upgrade. Anyway, changing a connection string to the proper oledb type string fixes one of the problems and the other is a bad practice problem that I will work on the database design to fix. Thanks for the response and let me know if you would like more details. David

]]>