April 8, 2009 at 3:44 pm
All,
Thank you very much for creating forums such as this for those of us who are, at best, journeymen.
I have an SS2K5 environment serving as the back-end for an MS Dynamics AX ERP application. I recently updated the value for a single record in a single column of a table. No errors, no problems. Upon realizing I had placed the incorrect value in the record I attempted to change it back to it's original value and received an error.
""Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.INVENTDIM' with unique index 'I_698DIMIDX'.
The statement has been terminated.""
How do I get the original value back in this field?
April 8, 2009 at 3:53 pm
twantoni (4/8/2009)
All,Thank you very much for creating forums such as this for those of us who are, at best, journeymen.
Greets 🙂
""Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.INVENTDIM' with unique index 'I_698DIMIDX'.
The statement has been terminated.""
Seems that the value already exists in another row. I have no yet idea about Dynamics but try to select from your table INVENTDIM with a WHERE condition for the value you want to set.
Flo
April 8, 2009 at 3:59 pm
Flo,
Thanks for the prompt response. Unfortunately the condition exists 42 times including my problem child. I don't believe the index is tied directly to the column I'm trying to update but I can't figure out what it is tied too.
April 9, 2009 at 4:43 am
Hi
In Management Studio Object Explorer select your table "dbo.INVENTDIM" expand the node and expand the "Indexes" node there should be an index called "I_698DIMIDX". Right click and select "Script Index As"->"CREATE To"->"New Query Editor Window".
Please post the script here than we can look for its uniqueness.
Greets
Flo
April 9, 2009 at 8:07 am
twantoni (4/8/2009)
All,""Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.INVENTDIM' with unique index 'I_698DIMIDX'.
The statement has been terminated.""
How do I get the original value back in this field?
Looks like 'I_698DIMIDX' is UNIQUE index and the column which you are trying to change teh value is par of this Index Definition.
Just see the value which you are trying to change is already part of teh table or not!!!
April 9, 2009 at 8:18 am
The context of the script is:
USE [LMC_AX4_Production]
GO
/****** Object: Index [I_698DIMIDX] Script Date: 04/09/2009 09:15:54 ******/
CREATE UNIQUE NONCLUSTERED INDEX [I_698DIMIDX] ON [dbo].[INVENTDIM]
(
[DATAAREAID] ASC,
[CONFIGID] ASC,
[INVENTSIZEID] ASC,
[INVENTCOLORID] ASC,
[INVENTLOCATIONID] ASC,
[INVENTBATCHID] ASC,
[WMSLOCATIONID] ASC,
[WMSPALLETID] ASC,
[INVENTSERIALID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
April 9, 2009 at 8:41 am
twantoni (4/8/2009)
Flo,I don't believe the index is tied directly to the column I'm trying to update but I can't figure out what it is tied too.
Just because you are updating only one column, if all the columns combined violate the uniqueness, it is an error. SQL doesn't just toss these errors out randomly but are based upon your constraints.
-- You can't be late until you show up.
April 9, 2009 at 1:17 pm
Terry,
I'm sorry I really don't understand indexes very well. Consequently the script I inserted in my last post might as well have been in Chinese, it really doesn't mean much to me. Would it be possible for you to explain your comment regarding the uniqueness of a record? There is only one record in the table with the correct DIMID, it's the one I'm trying to modify. Does my script indicate there is another field or combination of fields which are required to be unique?
Any help you can provide is greatly appreciated.
April 9, 2009 at 1:43 pm
The combination of all your key fields needs to be unique:
DATAAREAID],
[CONFIGID],
[INVENTSIZEID],
[INVENTCOLORID],
[INVENTLOCATIONID],
[INVENTBATCHID],
[WMSLOCATIONID],
[WMSPALLETID],
[INVENTSERIALID]
-- You can't be late until you show up.
April 9, 2009 at 2:08 pm
Hi
Little explanation:
An UNIQUE INDEX (independent if CLUSTERED or NONCLUSTERED for the moment) is a unique key.
Simplified a table is like a list of objects. You are able to create indexes and/or uniqueness. A unique index is like a key in a C# Dictionary or Hashtable. Let's say you have a Dictionary which gets as key a custom struct and the struct has the fields/properties which are defined in the unique index. You are not allowed to add the same key into your dictionary twice.
Here a simple translation of a C# snipped to TSQL.
C#
class Program
{
struct MyKey
{
public MyKey(int dataAreaId, int configId, int inventSizeId)
{
DataAreaId = dataAreaId;
ConfigId = configId;
InventSizeId = inventSizeId;
}
public int DataAreaId;
public int ConfigId;
public int InventSizeId;
public override int GetHashCode()
{
return DataAreaId + ConfigId + InventSizeId;
}
public override bool Equals(object obj)
{
if (obj is MyKey)
{
MyKey key = (MyKey)obj;
if (key.ConfigId == this.ConfigId
&& key.DataAreaId == this.DataAreaId
&& key.InventSizeId == this.InventSizeId)
return true;
}
return false;
}
}
static void Main(string[] args)
{
Dictionary<MyKey, string> dict = new Dictionary<MyKey, string>();
MyKey key;
key = new MyKey(1, 1, 1);
dict.Add(key, "Hello World");
key = new MyKey(2, 1, 1);
dict.Add(key, "Hello World 2");
// Exception:
key = new MyKey(1, 1, 1);
dict.Add(key, "Hello World 3");
}
}
TSQL
USE tempdb
GO
IF (OBJECT_ID('tempdb..#test') IS NOT NULL)
DROP TABLE #test
GO
-- Create table
CREATE TABLE #test
(
DataAreaId INT,
ConfigId INT,
InventSizeId INT
)
-- Create unique index
CREATE UNIQUE
INDEX [I_698DIMIDX]
ON #test (DataAreaId, ConfigId, InventSizeId)
INSERT INTO #test (DataAreaId, ConfigId, InventSizeId)
SELECT 1, 1, 1
INSERT INTO #test (DataAreaId, ConfigId, InventSizeId)
SELECT 2, 1, 1
-- Error
INSERT INTO #test (DataAreaId, ConfigId, InventSizeId)
SELECT 1, 1, 1
GO
-- Switch to "Results" tab to see the first two rows where inserted
SELECT * FROM #test
You can execute the TSQL script in Management Studio. It creates a temporary table in your tempdb. The table will automatically removed when you close your query window.
Hope this helps!
Maybe you should read some TSQL tutorials or how-to's. You don't have to read books with hundreds of pages but a main basic would help. Especially to you! It helps understanding the answers you get. This forum is called "Newbies" forum and that's exactly what it is, all members in this area really try to explain what they are doing but some real basics should be available.
Best wishes!
Flo
April 9, 2009 at 2:21 pm
Flo & Terry,
Thank you very much for your help and suggestions. The lightbulb went off after reading both of your last posts.
The whole time I was looking for a record with a unique index which included 'L-092' when I should have been looking for a record without 'L-092' because this is what I was changing it too, from L-092 to blank. As soon as I looked for records with a blank BATCHID I found the duplicate.
Thank you again.
April 13, 2009 at 6:12 am
Could you post the original update command you were issuing which was causing the error?
April 13, 2009 at 6:26 am
twantoni (4/9/2009)
The lightbulb went off after reading both of your last posts.
That's the beauty of these forums. A little nudge in the right direction and things come into focus. Then all is right in the world, for a few moments anyway. 😉 Glad we could help.
-- You can't be late until you show up.
April 13, 2009 at 10:28 am
rgillings (4/13/2009)
Could you post the original update command you were issuing which was causing the error?
UPDATE INVENTDIM SET INVENTBATCHID='L-092'
WHERE INVENTDIMID='IDN-008354'
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply