thomp361
asked on
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?
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?
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?
Are there triggers on the table?
ASKER
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!
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!
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 ;-)
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 ;-)
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.
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.
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.
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.
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.
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.
> 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
Hmm...
http://s7.quicksharing.com/v/816216/p1.PNG.html
...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;
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.
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.
> 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 :-)
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 :-)
Laughing - I agree.
ASKER
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!
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!
Run profiler with all tsql events enabled, and see what's going on there.
ASKER
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,
int,@cngName nvarchar(4),@cngDesc nvarchar(4),@cngType smallint,@cngQuery nvarchar(4)',@cng_cstID=80
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
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,@Pa
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
I am not too sure what I should be looking for:
INSERT:
exec sp_executesql N'INSERT INTO tblContactGroup(cng_cstID,
int,@cngName nvarchar(4),@cngDesc nvarchar(4),@cngType smallint,@cngQuery nvarchar(4)',@cng_cstID=80
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
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,@Pa
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
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?
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?
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks for you help!
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.
"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.
There are known issues with EM.