Unable to edit data in table in Management Studio 2005
LenaMsdn08
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?
Mark Han[MSFT]
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/details.aspx?FamilyID=ae7387c3-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: msd...@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/aa948874.aspx
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
LenaMsdn08
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)
LenaMsdn08
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!
Ekrem Önsoy
This is your current (3050) version.
--
Ekrem Önsoy
"LenaMsdn08" <LenaM...@newsgroup.nospam> wrote in message
news:FBA5FB76-5127-4F4A...@microsoft.com...
Mark Han[MSFT]
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: msd...@microsoft.com.
=========================================================
LenaMsdn08
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?
Mark Han[MSFT]
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: msd...@microsoft.com.
=========================================================
Mark Han[MSFT]
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/library/ms177523(SQL.90).aspx
insert: http://msdn.microsoft.com/en-us/library/ms174335(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.
LenaMsdn08
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]
Thank you for the update and cooperation.
The issue is so strange. Based on my research, when we use GUI to add data
into the table, the SQL Server will also execute insert. and based on your
description, I know that we can use the same SSMS to use the same method to
add data into the same database under another SQL Server instance.
I understand that you have a busy schedule. according to the current
situation, it is OK if it is not convenient to you to involve another
machine to do the test. if it is convenient to you, please send me an email
and the I can create a FTP(workspace) for you to upload the information
requested.
My email address is v-fa...@online.microsoft.com (remove the onlien)
in order to further diagnose the issue, we need to capture the SQL Server
Profiler Trace. I would like to compare the problematic process(Standard
instance) with the normal process(Express instance). after I receive your
email, I will list the detail steps to captue the profile trace.
I look forward to hearing from you.
Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
ESM
I tried this on my SQL Server 2005 Developer (9.0.1399). I used the script
and data given in the original post by Lena.
After creating the table, I did "Open Table" and entered the given data.
After tabbing out of the row, a red exclamation mark was placed in the row
pointer cell. Clicking on it showed the message in the status bar that row
retrieve failed. So I closed the query window, and again opened table. The
inserted data was there.
Then I inserted another row. Same warning. Again closing the window and
reopening showed both the rows. Modified this 2nd row. There was no problem.
However this is the only instance on this m/c.
So I tried this on another m/c, an x64 Vista Premium, which has an instance
of SQL Server 2005 Express installed by the mfgr. Over that I had installed
a SQL Server 2008 Developer Ed (10.0.1600) just to check if the presence of
SQL 2K5 Ex will have any effect. But I got the same warning and behavior.
I don't know if this will be useful.
ThanQ...
"LenaMsdn08" <LenaM...@newsgroup.nospam> wrote in message
news:177B2A85-18BA-4BD8...@microsoft.com...
Mark Han[MSFT]
Thank you for the posting.
According to your description, the issue you meet is different with Lena.
Based on my experience, the issue you meet is by-design and the waring is
just a kind remind, you can savely ingore it. I meet this kind of issue
before.
Thank you again.
ESM
A warning is issued, when there is a potential threat / erroneous operation.
Sorry, I am working on SQL server after a gap of 7 years. What are the
issues involved in warning "failed retrieving rows"? Why it is happening
only on insertion and not on editing rows?
ThanQ...
"Mark Han[MSFT]" <v-fa...@online.microsoft.com> wrote in message
news:tzV8DpRq...@TK2MSFTNGHUB02.phx.gbl...
Mark Han[MSFT]
Thank you for the update. sometimes, the warning tis indicate a potential
threat / erroneous operation. however this waring is just a kind remind.
in order to address your concern, I would like to explain my understanding
on this design
To use SSMS to open the table; and enter data into the table, before we
click the next row, these data we enter is not really inserted into the
table. Besdies, sine we are inserting data into the table, there is a lock
trigged on the row. So the SQL Server show a remind with( row retrieve
failed).
Hope the above helpful
Besides, Lena I haven't heard from you for a few days. if it is convenient
to you, could you send me a note to tell me the status of the issue.
I look forward to your update.
Best regards,
Lonnie Koenig
I just wanted to add to this conversation thread. I have the same
problem and I didn't have it last week.
I have run SSMS on a workstation and on the server itself and have
received the following error when inserting/updating a row:
The updated row has changed or has been deleted since data was last
retrieved.
I was able to modify rows in all of the tables before and now I can't.
After reading through the thread, I have a table with the following
definition:
CREATE TABLE [dbo].[tmpMassEmail](
[id] [int] IDENTITY(1,1) NOT NULL,
[emailto] [varchar](200) NOT NULL,
[emailfrom] [varchar](200) NULL,
[employer_id] [int] NULL,
[email_type] [varchar](50) NULL,
CONSTRAINT [PK_tmpMassEmail] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Notice:
1) It has a primary key
2) There are no Text or NText fields
When I capture the info in SQL Profiler, I get the following:
exec sp_executesql N'INSERT INTO tmpMassEmail(emailto) VALUES
(@emailto)',N'@emailto nvarchar(13)',@emailto=N'ja...@jack.com'
When I copy that script into a blank query window, it works.
Add me to the list of people who think that the tool should work as if
it were generating the correct sql behind the scenes like I typed it in.
If I wanted a SQL database that I had to write manual scripts for every
change I made, I would use Oracle.
Let's nail this one down and get it fixed!
Let me know if I can help...
Cheers!
Lonnie
*** Sent via Developersdex http://www.developersdex.com ***
Mark Han[MSFT]
Thank you for the post.
according to your description, yes, you meet the same issue dicussed in the
post and I'm working with Lena by email to further narrow down the issue.
based on my test, in the sql profile trace, I found the same SQL command as
the one you found. So the issue is very strange and hard to re-produce. So
to further narrow down the issue, could you please do the following test.
1 involve a new machine and setup the SQL Server 2005 Client Tool; update
the client tool to SP3 and update the SQL Server to SP3; and then use the
new SSMS installed on the new machine to connect to the SQL Server instance
to see if the issue will heppen.
after the above done, if the issue doesn't happen, the cause of the issue
is that the SSMS doesn't work normally and we might need to re-install the
netframwork.
on the other hand, if the issue still occur, the issue might be related to
the SQL Server instance/database.
2 For the same instance where the issue happens, if you insert into another
database by SSMS, does the issue happen?
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: msd...@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
Paul Shapiro
in the table, it might still generate the "updated row has changed" error.
If so, adding Set NoCount On at the beginning might help.
"Lonnie Koenig" <lon...@orlandojobs.com> wrote in message
news:u8VjrsBy...@TK2MSFTNGP06.phx.gbl...
Lonnie Koenig
rhyme or reason and here's what I found...
So, I created a new table:
CREATE TABLE [dbo].[test](
[PK] [int] NOT NULL,
[Test] [varchar](50) NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
No defaults or anythings out of the ordinary. This still doesn't work.
There are no triggers, constraints, computed columns or anything...
Still doesn't work...
Lonnie koenig
Sorry for the delay... I have been doing some research on this between
projects... I have a brand new workstation with a unmodified SSMS
client install on it.
Workstation:
Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Microsoft Data Access Components (MDAC) 6.1.7600.16385
(win7_rtm.090713-1255)
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 8.0.7600.16385
Microsoft .NET Framework 2.0.50727.4927
Operating System 6.1.7600
Server:
SQL Server Version 9.00.4053.00 SP3 Enterprise Edition
Created a new database (TestDB)
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N'TestDB', FILENAME = N'D:\{PATH}\TestDB.mdf' , SIZE = 2048KB ,
MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDB_log', FILENAME = N'D:\{PATH}\TestDB_log.ldf' , SIZE =
7168KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'TestDB', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [TestDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TestDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TestDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestDB] SET ENABLE_BROKER
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TestDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TestDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestDB] SET READ_WRITE
GO
ALTER DATABASE [TestDB] SET RECOVERY FULL
GO
ALTER DATABASE [TestDB] SET MULTI_USER
GO
ALTER DATABASE [TestDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestDB] SET DB_CHAINING OFF
Then I created a new table:
CREATE TABLE [dbo].[test](
[PK] [int] NOT NULL,
[Test] [varchar](50) NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Same issue exists. Still cannot edit rows in place.
Thanks!
Erland Sommarskog
> Sorry for the delay... I tried doing some research on this to find a
> rhyme or reason and here's what I found...
>
> So, I created a new table:
>
> CREATE TABLE [dbo].[test](
> [PK] [int] NOT NULL,
> [Test] [varchar](50) NULL,
> CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
> (
> [PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
>
> No defaults or anythings out of the ordinary. This still doesn't work.
> There are no triggers, constraints, computed columns or anything...
but when I created your table I had no problems with it.
Then again, about the only time I use Open Table is when I answer
questions about it on the newsgroups. I much prefer to use INSERT
statements, as they can be re-run - and I don't have to guess what
a GUI is doing behind my back.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Lonnie koenig
The real issue seems to be rather difficult to reproduce as it is spotty
and when it happens, seems to have no real reason why it is failing.
Although I agree, being a DBA for more years that I care to recall,
writing a sql statement for works and I don't have to depend on what a
GUI generates, but, this case is a bit different for me.
In some situations you want to simply change an active flag or something
you are seeing in a table. You should be able to use the tool (as you
always have been able to in the past) to edit that value when you see it
and trust that the tool (made by the same company as the server itself)
would generate at the very least a sql statement that works. I'd even
take it a step further and say (since it is made by the same company as
the server itself) the sql it generates should be optimized. In this
case, neither is true because it can't execute it's own sql.
It generates the following sql captured from SQL Profiler (from my post
on 4/28/2009):
exec sp_executesql N'INSERT INTO tmpMassEmail(emailto) VALUES
(@emailto)',N'@emailto nvarchar(13)',@emailto=N'ja...@jack.com'
That appears OK, however it generates an error (The updated row has
changed or has been deleted since data was last retrieved). However,
when you copy this code to a new query, it works correctly.
So, the real question is why does it work in a new query, but not work
in the table you open?
Now, also understand that a week before my post on 4/28/2009, all of
this worked... For some strange reason, it just started, although
nothing changed... No service pack updates, no patches to OS or SQL
Server, just strange stuff...
That's the real question...
Thanks for the reply.
Erland Sommarskog
> In some situations you want to simply change an active flag or something
> you are seeing in a table. You should be able to use the tool (as you
> always have been able to in the past) to edit that value when you see it
> and trust that the tool (made by the same company as the server itself)
> would generate at the very least a sql statement that works.
Maybe, but to be honest I don't have that trust. Microsoft have never
been able to get this fancy GUI stuff right. There is some stuff in
Mgmt Studio which is really horrible as the Table Designer.
> It generates the following sql captured from SQL Profiler (from my post
> on 4/28/2009):
>
> exec sp_executesql N'INSERT INTO tmpMassEmail(emailto) VALUES
> (@emailto)',N'@emailto nvarchar(13)',@emailto=N'ja...@jack.com'
>
> That appears OK, however it generates an error (The updated row has
> changed or has been deleted since data was last retrieved). However,
> when you copy this code to a new query, it works correctly.
If you study the Profiler trace more closely, you will find that there
is a second batch where it reads the recently submitted data. I seem to
recall that this code has some problems, although I don't remember the
details.
Lonnie koenig
The second batch you are referring to is the "re-selection" of changed
data thereby rolling the changes back.
As an example, here is the entire sql profile batch for the change
itself. I waited for over an hour for any additional batches to be
created just to make sure. Line breaks my mess it up, so copy it and
paste it into notepad or SSMS if you'd like.
select col.name, st.name as DT_name, case when (st.name in ('nchar',
'nvarchar') and (col.max_length > 0)) then col.max_length / 2 else
col.max_length end, col.precision, col.scale, bt.name as BT_name,
col.is_nullable, col.is_identity,col.is_rowguidcol,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as
is_computed, case when(cmc.column_id is null) then null else
cmc.definition end as formular, col.collation_name, col.system_type_id
from TestDB.sys.all_columns col left outer join TestDB.sys.types st on
st.user_type_id = col.user_type_id left outer join TestDB.sys.types bt
on bt.user_type_id = col.system_type_id left outer join
TestDB.sys.identity_columns idc on idc.object_id = col.object_id and
idc.column_id = col.column_id left outer join
TestDB.sys.computed_columns cmc on cmc.object_id = col.object_id and
cmc.column_id = col.column_id where col.object_id =
object_id(N'TestDB.dbo.test') order by col.column_id
go
SELECT @@LOCK_TIMEOUT
go
use [TestDB]
go
SELECT
db_name() AS [Database_Name],
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Name],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
i.is_unique AS [IsUnique],
i.fill_factor AS [FillFactor],
CAST(INDEXPROPERTY(i.object_id, i.name, N'IsPadIndex') AS bit) AS
[PadIndex],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
CAST(INDEXPROPERTY(i.object_id,i.name,N'IsFulltextKey') AS bit) AS
[IsFullTextKey],
CASE WHEN 'FG'=dsi.type THEN dsi.name ELSE N'' END AS [FileGroup],
s.no_recompute AS [NoAutomaticRecomputation],
CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex],
i.is_disabled AS [IsDisabled],
CAST(CASE WHEN 'PS'=dsi.type THEN 1 ELSE 0 END AS bit) AS
[IsPartitioned],
CASE WHEN 'PS'=dsi.type THEN dsi.name ELSE N'' END AS [PartitionScheme]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical =
0) AND (i.object_id=tbl.object_id)
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id =
i.data_space_id
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND
s.object_id = i.object_id
WHERE
(SCHEMA_NAME(tbl.schema_id)=N'dbo' and tbl.name=N'test')
ORDER BY
[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
go
SELECT @@LOCK_TIMEOUT
go
use [TestDB]
go
SELECT
db_name() AS [Database_Name],
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Index_Name],
clmns.name AS [Name],
(case ic.key_ordinal when 0 then cast(1 as tinyint) else ic.key_ordinal
end) AS [ID],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N'IsComputed') AS bit) AS
[IsComputed],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical =
0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and
(ic.key_ordinal > 0 or ic.partition_ordinal = 0)) AND
(ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and
clmns.column_id = ic.column_id
WHERE
(i.name=N'PK_test')and((SCHEMA_NAME(tbl.schema_id)=N'dbo' and
tbl.name=N'test'))
ORDER BY
[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Index_Name]
ASC,[ID] ASC
go
exec sp_executesql N'INSERT INTO test(PK, Test) VALUES (@PK,
@Test)',N'@PK int,@Test nvarchar(10)',@PK=1,@Test=N'AddRowTest'
go
>>Maybe, but to be honest I don't have that trust.
>>Microsoft have never been able to get this fancy GUI >>stuff right.
There is some stuff in Mgmt Studio which
>>is really horrible as the Table Designer.
Errr, I think this is what this thread is about, so this is restating
the issue.
The point is Microsoft SQL Server is NOT free, as a matter of fact, it
is quite a few American Dollars. I would understand (a little, but very
little) if it were the "free" SSMS Express Edition, but this is the full
product and I just want a tool that works for my money!
If MySQL can do it, why can't Microsoft?
Hopefully, someone has an answer out there somewhere...
Thanks!
Erland Sommarskog
> The second batch you are referring to is the "re-selection" of changed
> data thereby rolling the changes back.
>
> As an example, here is the entire sql profile batch for the change
> itself. I waited for over an hour for any additional batches to be
> created just to make sure. Line breaks my mess it up, so copy it and
> paste it into notepad or SSMS if you'd like.
But in this case there is no re-selection, huh? Is that the problem?
If fails to run the reselection batch, and then forgot that it hasn't?
> The point is Microsoft SQL Server is NOT free, as a matter of fact, it
> is quite a few American Dollars. I would understand (a little, but very
> little) if it were the "free" SSMS Express Edition, but this is the full
> product and I just want a tool that works for my money!
>
> If MySQL can do it, why can't Microsoft?
>
> Hopefully, someone has an answer out there somewhere...
I am not going to try it. All I can say is that the engine is of far better
quality than the tools.
Thankfully, Query Editor works OK, and that is the most important tool for
me. But then it also has a simple job do to: run the queries I tell it to
and present the results.
Lonnie koenig
>>But in this case there is no re-selection, huh?
>>Is that the problem?
>>If fails to run the reselection batch, and then forgot
>>that it hasn't?
I left it at the prompt saying the row changed between retrieve & update
for the hour. So, the reselection never happened as this doesn't happen
until after you click OK to clear the box.
>>I am not going to try it. All I can say is that the
>>engine is of far better quality than the tools.
I agree, I am trying to get the tools to be that good too, or at least
someone to explain why it doesn't...
Erland Sommarskog
> I left it at the prompt saying the row changed between retrieve & update
> for the hour. So, the reselection never happened as this doesn't happen
> until after you click OK to clear the box.
Error:UserMessage to the trace, and see if this reveals something.
Koenig@discussions.microsoft.com Lonnie Koenig
context to 'TestDB'."
Strange....
Thanks!
Lonnie
"Erland Sommarskog" wrote:
> .
>
Lonnie koenig
I FIGURED IT OUT!!!
OK, it's a little strange, but the problem for me was in the database
server connection properties.
So in Microsoft SQL Server Management Studio, right-click on the
database server and click on Properties. Then select Connections in the
left pane under "Select a Page". Make sure "No Count" is NOT checked
under "Default Connnection Options". Click on OK and restart Database
Server Services.
You should now be able to update data in the grid...
Now, does any one know why this is the case???
Cheers!
Lonnie
Erland Sommarskog
> SOLVED!!!
>
> I FIGURED IT OUT!!!
>
> OK, it's a little strange, but the problem for me was in the database
> server connection properties.
>
> So in Microsoft SQL Server Management Studio, right-click on the
> database server and click on Properties. Then select Connections in the
> left pane under "Select a Page". Make sure "No Count" is NOT checked
> under "Default Connnection Options". Click on OK and restart Database
> Server Services.
>
> You should now be able to update data in the grid...
>
> Now, does any one know why this is the case???
INSERT, UPDATE or DELETE statement. In many casees, this rowcount is
only network chatter, and it's best practice to include SET NOCOUNT ON
in stored procedures. With some client APIs, the rowcount can also
confuse a client that expects a result set, but is not aware that the
procedure first produces a rowcount for an insert into a temp table or
similar.
However, there are tools that do make use of the rowcount, and apparently
Open Table in SSMS is one of them.
But I would agree that it's kind of crappy. Since it depends on it, I
think it should turn off SET NOCOUNT to avoid this situation. I will
need to research a little more in newer version, and possible enter a
Connect item. Not tonight though.
Lonnie koenig
knew it wasn't just random...
Thanks for hanging in there with me!
Erland Sommarskog
> However, there are tools that do make use of the rowcount, and apparently
> Open Table in SSMS is one of them.
>
> But I would agree that it's kind of crappy. Since it depends on it, I
> think it should turn off SET NOCOUNT to avoid this situation. I will
> need to research a little more in newer version, and possible enter a
> Connect item. Not tonight though.
I researched this a little more. First, I did not read your message
carefully, but I thought it was a setting for SSMS you had changed.
Infact it is a setting for the entire server. If it had been SSMS's own
setting, it would be bad if it tripped itself. But I don't think it
is common to set the "user options" configuration option, so this
seems like a marginal case.
Furthermore, when I tested in SQL 2008 R2 I was not able to repeat the
behaviour. It seems that SSMS 2008 always read back the data and does
not rely on SQL Server returning any rowcount.