SQL Server 2008: FIXED “Data has changed since the Results pane was last retrieved”

Today I was trying to edit the value in a table cell through the query browser in Microsoft SQL Server Management Itudio and I kept getting the following error:

Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

I would click “Yes”, and get another error:

No row was updated.
The data in row X was not committed.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(N rows).
Correct the errors and retry or press ESC to cancel the change(s).

I was killing myself trying to figure out what was wrong. I was the only person working on the database, so the data wasnt being changed behind my back.

After an hour on goolge I came across a Microsoft tech article for MS SQL 2005, http://support.microsoft.com/kb/925719

It stated the issue could occure from one of the following conditions:

• The table contains one or more columns of the text or ntext data type.
• The value of one of these columns contains the following characters:
• Percent sign (%)
• Underscore (_)
• Left bracket ([)
• The table does not contain a primary key.

I double checked the table I was working on and realized it didnt have a primary key. It must have gotten dropped in the data migration from MS SQL 2000 to MS SQL 2008. A simple right click, set primary key and my problem was fixed.

Verify the primary keys! It’s a simple fix to a head breaker of a problem. I hope someone finds this and it fixes their issue.

Tagged as: 2008, database, ms sql, ssms