Lonnie Koenig (lonnie (AT) orlandojobs (DOT) com) writes:
What exactly is happening? Yes, I see some pieces in prevoius posts,
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, 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
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland,

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'jack (AT) jack (DOT) 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.

Lonnie



*** Sent via Developersdex http://www.developersdex.com ***

Lonnie koenig (anonymous (AT) devdex (DOT) com) writes:
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.

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.




--
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
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Erland,

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'IsFulltext Key') 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




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!

Lonnie

*** Sent via Developersdex http://www.developersdex.com ***

Lonnie koenig (anonymous (AT) devdex (DOT) com) writes:
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 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.

--
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
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

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 agree, I am trying to get the tools to be that good too, or at least
someone to explain why it doesn't...

*** Sent via Developersdex http://www.developersdex.com ***

Lonnie koenig (anonymous (AT) devdex (DOT) com) writes:
Hm... One more thing to try: add the events Error:Exception and
Error:UserMessage to the trace, and see if this reveals something.


--
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
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

The only "Error" message I get is a User Error Message of "Changed database
context to 'TestDB'."

Strange....

Thanks!

Lonnie

"Erland Sommarskog" wrote:

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???

Cheers!

Lonnie

*** Sent via Developersdex http://www.developersdex.com ***

Lonnie koenig (anonymous (AT) devdex (DOT) com) writes:
By default, SQL Server produces a rowcount when you perform an
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.

--
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
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx