I'm having trouble editing data in a table in SSMS 2005, the way one could do
in Enterprise Manager. For example, I have this test table:

CREATE TABLE [dbo].[TestTable](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_TestTable_CreatedOn]
DEFAULT (getdate()),
[associateID] [varchar](512) NULL,
[employeeID] [varchar](512) NULL,
[fname] [varchar](512) NULL,
[lname] [varchar](512) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

with one line of data
2 2008-08-01 00:25:57.570 158045 12345678 Lena Nilsson

In the left-hand pane I right-click the name of the table and select "Open
Table". Then I go in, click in one of the fields, make a change, and click on
another row to save it. Instead I get an error message "Data has changed
since the Results pane was last retrieved. Do you want to save your changes
now? (Optimistic Concurrency Control Error)" . When I click "Yes" to update
anyway, I get another error "No row was updated. ... Error Source:
Microsoft.VisualStudio.DataTools. Error Message: The updated row has changed
or been deleted since data was last retrieved." So far I have not been able
to make it save the data.

I've seen an article in the Knowledge base (KB925719) about this error, but
none of those circumstances apply to my table. According to the KB article,
this issue occurs if the following conditions are true:

* 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:
o Percent sign (%)
o Underscore (_)
o Left bracket ([)
* The table does not contain a primary key.

I don't see any of those conditions applying to my table.

The system informatiion from SSMS Help, About is
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.3082
Operating System 5.1.2600

How can I get around this error?

Hi Lena,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that after creatig table test table, we
will receive the following error when insert/update data into the table
manually
If I have misunderstood, please let me know.

"Data has changed since the Results pane was last retrieved. Do you want
to save your changes now? (Optimistic Concurrency Control Error)"

Based on my research, the issue might be a known issue, please try to
update the SQL Server to SP3.
please download the SQL Server 2005 SP3 via the following
http://www.microsoft.com/downloads/d...7c3-348c-4faa-
8ae5-949fdfbe59c4&DisplayLang=en

To workaround the issue, please try to insert/update the date by T-SQL.

If there is anything unclear, please do not hesitate to let me know. Have a
nice day.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx

================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Mark,

Thank you for the help! I checked @@VERSION on the server and it only has
SP2, so I'll update it and see.

The release notes for SP3 say "SQL Server 2005 SP3 contains the hotfixes
that were included in cumulative update packages 1 through 9 for SQL Server
2005 SP2. If you are upgrading from SQL Server 2005 SP2 Cumulative Update 10
or Cumulative Update 11, you must apply a post-SP3 cumulative update after
you upgrade to SP3 to obtain all the hotfixes."

How can I tell if the server has Cumulative Update 10 or 11 installed? (I
"inherited" this server from someone else and don't know what was installed
on it.) @@VERSION returned:

Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86)
Mar 2 2007 20:01:28
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


"Mark Han[MSFT]" wrote:

Mark,

I may have answered my own question. The first line of @@VERSION data was
Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86)

After some more digging, I found http://support.microsoft.com/kb/956854
(Cumulative update package 10) which says "This build of the cumulative
update package is also known as build 9.00.3294"

Likewise for Cumulative update package 11,
http://support.microsoft.com/kb/958735/ says "Note This build of the
cumulative update package is also known as build 9.00.3301"

So, since I only have 9.00.3050.00, I don't have either one of the above,
and I'm probably missing a ton of other updates as well - right?

I'm going to assume that is the case and install SP3. Thank you for your
help with this!

"LenaMsdn08" wrote:

Hi Lena,

Thank you for the update. I'm glad that you find the answer yourself.

Based on your description, I know that the version of the sql is 3050. it
is the Post-GDR update version. So to install SP3 is OK for you.

Besides, if the issue happens again after installing SP3, pleaselet me
know. I will help to assist you with the issue.

I look forward to hearing from you. Thanks.


Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Mark and Ekrem,

Thank you for helping me out with this! Unfortunately, even after installing
SP3 on the server and rebooting after the install, I still keep getting the
same errors every time I try to edit table data. @@version now contains

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


I have a copy of SQL Server 2005 Express installed on my workstation that
looks like it is the same build as on the server. Its @@version is

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

When I connect to my local instance of SQL Server Express, I can edit table
data in SSMS without any errors or problems. The instance on the server is
Standard edition, is it possible that SQL Server Express supports editing
tables in SSMS but Standard does not?



"Ekrem Önsoy" wrote:

Hi Lena,

I'm Mark Han. I'm writing to follow up the post.

I would like to know if the issue happens after installing SP3.

I look forward to hearing from you.
Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Mark,

Thank you for asking! Unfortunately, I am still getting the exact same error
after installing SP3.

I have also upgraded from Windows Update to Microsoft Update on the server
and ran that, but did not find any additional updates to SQL Server 2005.

@@Version now returns
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


"Mark Han[MSFT]" wrote:

Hi Lena,

Thank you for the update.

Based on the current situation, the issue might be related to the SSMS. So
please do the following to narrow down the issue.
1) please use T-SQL to insert/update the date . there are several articles
to share with you.
update: http://msdn.microsoft.com/en-us/libr...3(SQL.90).aspx
insert: http://msdn.microsoft.com/en-us/libr...5(SQL.90).aspx

2) please try to involve another machine, install SSMS and try to
re-produce the issue

Besides, I followed the steps posted and the issue doesn't happen. So the
issue you meet should be s special one.

I look forward to your update.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Mark,

Thank you for your reply! This is a partial response - I'm having a busy day
and won't have time to install SSMS on a different computer until later.

What I tried was take the test database on the server offline, copy the .mdf
and .ldf files to the instance of SQL Server 2005 Express that runs on my
local PC, and attach them. Using the same instance of SSMS, I was able to
open my test table in this copy of the database and add or edit records
without any errors.

@@Version on my local copy of SQL Server Express returns
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)


Using T-SQL queries to add or edit records to the test table on the server
also works fine - unfortunately for the data entry work I do, writing queries
takes a lot more time than simply opening the table and typing.

I will post an update when I have installed SSMS on a different computer and
tried updating the table from there.

"Mark Han[MSFT]" wrote: