Solved

Optimistic Concurrency Control Error

Posted on 2006-06-29
20
1,256 Views
Last Modified: 2011-09-20
Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

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

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?
0
Comment
Question by:thomp361
  • 10
  • 5
  • 5
20 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 17010879
Do not use Enterprise Manager for this. Use Query Analyzer and create t-sql. This link will explain the differences: http://www.aspfaq.com/show.asp?id=2455

There are known issues with EM.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17010941
This should be happening - I just tried and sure it worked on; with the only note that you first update the row to desc= NULL (pressing ctrl-0), then you click another row so the row is updated, and only then you delete it.

Are there triggers on the table?
0
 

Author Comment

by:thomp361
ID: 17011154
There are no triggers on the table.

One other thing to note, if I create an exact replica of table X, then I do an insert, then an update(setting Desc to NULL, then a delete, I don't get the Optimistic Concurrency Control Error.  It seems like something is jacked with that table....is it something I should be worried about, doesn't seem like it should be happening.

As far as not using EM for this, if I do the insert/update/delete with my ADO.NET code I don't receive any errors back when trying to delete in the code. However if I do the insert/update in my code, then try the delete in EM, the error pops up.

 I guess I am just wondering why EM throws this error when trying to delete after my update.....it seems like something is wrong because other tables don't exhibit the same behavior.

Thanks for the help!


0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17011274
something must be different with this table. In the same EM, create a script of it, including everything, create a script of the replia table, and compare.

By the way, creating workarounds for "mystery" problems (like using QA instead of EM) usually only covers the problem, but the problem will still exist. If there's something misunderstood about the way things work, it will manifest itself in the most destructive way possible, whether you notice it or not. So you must resolve it. (this applies not only to sql server ;-)
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17011297
I guess I am just wondering why EM throws this error when trying to delete after my update.....it seems like something is wrong because other tables don't exhibit the same behavior.

EM is inconsistent when dealing with editing tables (as you have discovered). You should do your testing with QA instead. EM is good for managing the databases, but I have found the table/view designer to be problematic.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17011370
I have a whole slew of tools, including access and apexsql, and still EM remains my tool of choice to work with the data.

Of course it's not a production tool for users though. I understand, the asker is using it for the purposes of ad-hoc patching the data in the development process.

However, the very fact that something unusual is happening with this particular table, should be the red flag indicating problems that quite likely do manifest themselves in more serious ways. It happens very often that the developer is "clued" by some seemingly insignificant irregularity, but if you care to uncover the reason, you see how it is producing some very serious errors in really serious financial reports - so you praise your wisdom that you checked it out. It's always better to pay attention to the "insignificant" clues rather than wait until the significant dollars are noticed by the user.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17011382
By the way, creating workarounds for "mystery" problems (like using QA instead of EM) usually only covers the problem, but the problem will still exist.

True, but there are "mystery" problems that are caused by the limitations of using EM. For example, try to use a CASE statement in a view in EM; it will fail. The view designer in EM only supports a subset of t-sql, and CASE did not make that list. Does that mean that you cannot create views with CASE statements? No, you should not use EM when that is a requirement.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17011488
> The view designer in EM only supports a subset of t-sql, and CASE did not make that list. Does that mean that you cannot create views with CASE statements? No, you should not use EM when that is a requirement.

Hmm...

http://s7.quicksharing.com/v/816216/p1.PNG.html
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17011562
...besides, the fact that not everything EM can display in visual editor, does not mean that you can't work with the sql text in the same way as you work in QA. EM does not show the diagram for _some_ cases, but QA does not show it ever;
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17011596
vadimrapp1 - I agree, EM has its good points. Wait until you get your hands on Management Studio and you will not look back. EM was very good with managing databases and servers. I have never liked the "visual" query tools. I have always been comfortable with writing sql.

I would guess that the reason he is having problems with table X is because he is editing a ntext column. Text (or ntext) have their own rules and limitations. I would be curious to see if he has the same issue if he changed the data type of the column to nvarchar(up to 8,0000 characters) or he performs the operation on another table with the ntext data type.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17011663
> I have never liked the "visual" query tools. I have always been comfortable with writing sql.

that's the most important point :-)

> Wait until you get your hands on Management Studio and you will not look back

I actually beta tested sql05, and after it was finished, I finally uninstalled it with a big sigh of relief. The main sponsor of the sigh was exactly the management studio.

Different folks, different tastes :-)


0
 
LVL 27

Expert Comment

by:ptjcb
ID: 17011725
Laughing - I agree.
0
 

Author Comment

by:thomp361
ID: 17011932
Ok, I've gotten some very strange results....

I scripted both table X and the replica, they are exactly the same other than the name of the constraints.

on table X I changed the ntext field to an nvarchar() field, and the error message went away!  When I changed the field back to ntext, the error came back.

Still doesn't explain why my replica table doesn't have any problems with the ntext field.

On another table (that throws the concurrency error after update/delete), I tried setting non-ntext fields to null the deleting and it also throws the concurrency error.

One other note, I am using MS not EM.

Any other ideas guys!  Thanks for all of your help!

0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17013931
Run profiler with all tsql events enabled, and see what's going on there.
0
 

Author Comment

by:thomp361
ID: 17014421
I insert, then change cngDesc to NULL, then try and delete.....

I am not too sure what I should be looking for:

INSERT:
exec sp_executesql N'INSERT INTO tblContactGroup(cng_cstID, cngName, cngDesc, cngType, cngQuery) VALUES (@cng_cstID, @cngName, @cngDesc, @cngType, @cngQuery)',N'@cng_cstID
int,@cngName nvarchar(4),@cngDesc nvarchar(4),@cngType smallint,@cngQuery nvarchar(4)',@cng_cstID=8001,@cngName=N'asdf',@cngDesc=N'asdf',@cngType=0,@cngQuery=N'asdf'

UPDATE:
exec sp_executesql N'UPDATE tblContactGroup SET cngDesc = @cngDesc WHERE (cngID = @Param1) AND (cng_cstID = @Param2) AND (cngName = @Param3) AND (cngDesc LIKE @Param4) AND
(cngType = @Param5) AND (cngQuery LIKE @Param6)',N'@cngDesc nvarchar(4000),@Param1 int,@Param2 int,@Param3 nvarchar(4),@Param4 nvarchar(4),@Param5 smallint,@Param6
nvarchar(4)',@cngDesc=NULL,@Param1=2,@Param2=8001,@Param3=N'asdf',@Param4=N'asdf',@Param5=0,@Param6=N'asdf'

DELETE (attempt 1, before error)
exec sp_executesql N'DELETE FROM tblContactGroup WHERE (cngID = @Param1) AND (cng_cstID = @Param2) AND (cngName = @Param3) AND (cngDesc IS NULL) AND (cngType = @Param4) AND
(cngQuery LIKE @Param5)',N'@Param1 int,@Param2 int,@Param3 nvarchar(4),@Param4 smallint,@Param5 nvarchar(4)',@Param1=2,@Param2=8001,@Param3=N'asdf',@Param4=0,@Param5=N'asdf'

DELETE (attempt 2, after error)
exec sp_executesql N'DELETE FROM tblContactGroup WHERE (cngID = @Param6)',N'@Param6 int',@Param6=2


Note: cngQuery is an ntext, cngType is a smallint, cngName is nvarchar, and cngID and cng_cstID are ints
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17014540
1. Did you enable all TSQL events?

2. how do you update then delete? after you update, do you click on another row, then return to the 1st row and delete it? or you remain on the same row?
0
 

Author Comment

by:thomp361
ID: 17014876
Yes, I enabled all TSQL events...there seem to be alot of other events in the trace, but I can't make anything of the "textdata" for those events.

I update, click on another row, then go back to the row I just edited, then delete that row.

Thanks for helping me figure this one out!
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 17015387
if you click on another row, then the fact that you updated the first one should not matter - once you left the updated row, it's committed.

Try distancing deletion from updating. Click not one but several other rows, then return to the first one and delete. Close the application, reopen, delete.

If you are sure there are no triggers, then at this point I would probably open support incident with Microsoft. From the volume of steady stream of almost everyday fixes in it, I'd think that the chances that they wouldn't refund the incident fee are pretty slim - this definitely sounds like a bug in MS.

0
 

Author Comment

by:thomp361
ID: 17019390
Yup, no change.  I installed the new SP1 for it, didn't help it.  Do you have that MS incident number handy?

Thanks for you help!
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 17019428
support.microsoft.com

"Contact a support professional by e-mail, online chat, or telephone. Select a product to start"

If in the end it turns out to be a problem with sql server, or even something working as designed, but not clearly explained in the error message, make sure to request refund of the support fee in the end.
 
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now