Dear SQLers:

I have a couple of problems with Edit Top 200 Rows.

1) I call it up on one table, change one column value (not a key
column) from "Cheque" to "Check". When I try to exit the row, I get:

No row was updated.

The data in row 3 was not committed.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make
the row unique or they alter multiple rows(2 rows).

Correct the errors and retry or press ESC to cancel the change(s).

and an OK button.

2) After I have run Edit Top 200 Row and have closed it, I can not
rerun my database creation script because the database is supposedly
in use.

Have I done something wrong, or is this a bug?

Sincerely,

Gene Wirchenko

There's a setting in SSMS where you can change this 200 to unlimited.
That way, you can check your entire table for the multiple rows. The
screendump can be found at
http://www.hvandenberg.net/limitrowsinSSMS.bmp

Best,
Henk

On 13-04-2011 22:20, Gene Wirchenko wrote:

On Wed, 13 Apr 2011 22:56:58 +0200, Henk van den Berg
<hvandenberg (AT) xs4all (DOT) nl> wrote:

This has nothing to do with the problem. I can see the entire
table on the screen.

I have verified in the trigger that only one row is being changed
with Edit Top 200 Rows. If I remove the trigger, the problem is gone.
If I put the trigger back in but commenting out the parts that change
values, I get the problem again.

If I execute SQL statements in a script, they work fine. It is
only in Edit Top 200 Rows that it does not work.

[snipped previous]

Sincerely,

Gene Wirchenko

Does the trigger fire another trigger in turn?

Best,
Henk

On 13-04-2011 23:07, Gene Wirchenko wrote:

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
I don't that grid, and make all my data modifications with scripts.
Scripts can be reusable. And trying to second-guess SSMS which has
some funny quirks, is nothing I like.

Hint: you learn more SQL, if you use scripts.

I can say whether the error message you got makes sense or not, since
I don't know the table or the data.


I would guess this is regular connection pooling. That is, the client
API holds the connection for 60 seconds after disconnection so it
can be resued. This is nothing specific to SSMS.

The way out is

ALTER DATBASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

That kills all connections against the database.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

On Wed, 13 Apr 2011 23:23:56 +0200, Henk van den Berg
<hvandenberg (AT) xs4all (DOT) nl> wrote:

No.

[snip]

Sincerely,

Gene Wirchenko

On Wed, 13 Apr 2011 23:50:35 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

A missing word?
Well, I have been running just scripts, blowing away the database
each time to minimise error possibilities. I thought that I would try
checking the checking with Edit Top 200 Rows, because it would be
easier. <bitter>Yeah, right.</>

I know: it is my preference. And get bitten harder if you do
not?

It is a very simple table with one check for one column of "D" or
"C". It and two other columns get NiceString() done to them, but I
have the problem even when I reduce the trigger so that it is just
running through the cursor and writing it all without change.

I may try that. I might just avoid Edit Top 200 Rows.

My scripts use explicit database selection, including select
master at the end to disconnect.
use Banking
go
<stuff>
use master
go
A pity I can not do this with Edit Top 200 Rows.

Sincerely,

Gene Wirchenko