Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1460
  • Last Modified:

Optimistic Concurrency Control Error

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
thomp361
Asked:
thomp361
  • 10
  • 5
  • 5
1 Solution
 
ptjcbCommented:
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
 
Vadim RappCommented:
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
 
thomp361Author Commented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Vadim RappCommented:
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
 
ptjcbCommented:
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
 
Vadim RappCommented:
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
 
ptjcbCommented:
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
 
Vadim RappCommented:
> 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
 
Vadim RappCommented:
...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
 
ptjcbCommented:
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
 
Vadim RappCommented:
> 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
 
ptjcbCommented:
Laughing - I agree.
0
 
thomp361Author Commented:
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
 
Vadim RappCommented:
Run profiler with all tsql events enabled, and see what's going on there.
0
 
thomp361Author Commented:
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
 
Vadim RappCommented:
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
 
thomp361Author Commented:
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
 
Vadim RappCommented:
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
 
thomp361Author Commented:
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
 
Vadim RappCommented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 10
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now