web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Violation of PRIMARY K...
Microsoft Dynamics GP (Archived)

Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

(0) ShareShare
ReportReport
Posted on by Community Member

Hi All

Sorry to trouble all of you.

Can anyone help me on this issue, which my client had encounter after clicking at the Inquiry>>Item Bin Transfer or Bin Transfer?

Please see error message below for Item Bin Transfer

SQL Error:2627[Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of PRIMARY KEY contstraint 'PK##1533840'. Cannot insert duplicate key in object 'dbo.##1533840'. The duplicate key value is (1, 00000527      ).

The error message for Bin Transfer Primary Key is different – Please see error message below:

SQL Error:2627[Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of PRIMARY KEY contstraint 'PK##2195203'. Cannot insert duplicate key in object 'dbo.##2195203'. The duplicate key value is (1, 00000527      ).

I did all the maintenance, checklinks & Reconcile and the error still stay.

I really appreciate and thanking all of you in advance.

LC

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Not much to go on.  You'll need to figure out where the bad data is.  run a sql trace, and determine which tables are being used, examine the data for that item in question.  issue might be duplicate data, columns with the wrong values in them, etc.

  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hi Jon

    Thank you for suggestion.

    I checked the duplicates and found something very odd.

    I am writing to provide more information.

    Item No 34310MA-D was created in IV00101 on 9 Dec 2014.

    There were transactions in the bin already since then.

    Then somewhere, a user created a PO on 9 March 2016 and we noted that the SAME item number was created on 9/3/16 per the DEX_ROW TS for IV00101.

    What I do not understand is how a duplicate in IV00101 can be accepted in the system.

    The PO was received against the item created instead of the original.

    This is a fundamental duplicate that does not make sense.

    I did an adjustment to zeroise the item and then made it inactive; closed the PO, removed it to history. Performed PO reconcile and the Inventory reconcile for the item but the error persists.

    Any further suggestion re how I should proceed?  Suggestions much appreciated.

    Thank you.

  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hi Jon

    I believe it is impossible to have 2 same Item No, where CheckLinks and Reconcile didn’t printout error.

    After I exported from Smartlist to Excel, then I managed to see the different between these 2 Item No. The original Item No 34310MA-D, which was created on 9 Dec 2014 and Item No 34310MA-Dá (á=space) had a space behind.

    In the Inventory Inquiry windows all the others inquiry options have no problem except Item Bin Transfer and Bin Transfer option giving problem.

    But the error message still persists, don’t understand what causes it.  

    Appreciate and hopefully, Dynamics GP experts and developers can help me in this area.

    Many thanks in advance.

    LC

  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    The second item number, the one with the funky character in it, maybe has some bad ascii text in there.  if you use a hex editor you'll see the ascii numeric value of that funky character.  Applications can choke on the bad text, they were not designed to work with invisible text characters.  I think the only fix is to correct the bad data in IV00101,IV00101,IV00103, etc.  Call your GP vendor, they should be able to help, because this involves direct data updates, and this will be a bumpy ride!

  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hi Jon

    Correct me if I am wrong, I think there is nothing wrong with the Item No because if GP able to take in all the special character for Item No. So this Item No 34310MA-Dá and Item No 34310MA-D will consider as a unique Item No.

    I managed to find this website posted by David Musgrave on 12/05/2014. https://blogs.msdn.microsoft.com/developingfordynamicsgp/2014/05/12/violation-of-primary-key-constraint-cannot-insert-duplicate-key-in-object-error-when-using-item-list/

    I have copied and use his SQL script to look for duplicate key in the IV00101 and IV00102, but the result shown not duplicate key at all.   Now I will try to use his SQL script to check on IV00103.

    I am so sorry to trouble you and really hope somebody in the community that ever encountered this issue for help!

    Appreciate and many thanks.

    LC

  • Ken Hubbard Profile Picture
    Ken Hubbard on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hey LC,

    It looks like you are running into a quality issue that we documented for GP 2013 where multiple users using Item Bin Transfer for the same item can cause a duplicate on the IV30004 table. This does not cause a problem with the transaction, but when we build the temp table for viewing the this affected Item, it violates the primary key on the temp table.

    It looks like this problem is resolved in the latest version of 2013.

    You should be able to confirm that this is the issue through the following select against the company database in SQL:

    select Bin_XFer_Doc_Number, ITEMNMBR from IV30004 group by Bin_XFer_Doc_Number, ITEMNMBR having COUNT(*) > 1

    If this returns results you can update one of the duplicates to have a value +A. In this case you would update one of the records to have a Bin_XFer_Doc_Number of 00000527A

    If you need assistance with this I would open a support case and reference BUG 70440.

    Thank you,

    Ken Hubbard

    Microsoft Dynamics Support

  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hi Ken

    Appreciate and thank you very much. Now at least I see some light in the tunnel. I did exported this table IV30004 data into Excel. I did studied the data from this table IV30004.

    Yes, they do have duplicate for Bin_XFer_Doc_Number (290) with 4 records, but the SEQNUMBR is increment e.g. 16384, 32768, 49152 & 65536 for SERLTNUM (OT22, OT32, OT47 & OT47) & SELTQTY (2, 1, 1, & 1) and DEX_ROW_ID = (1, 2, 3, &4).

    Correct me if I am wrong, I thought although the Bin_XFer_Doc_Number are duplicated but the SEQNUMBER is incremental. Therefore this record is unique.

    I copied your SQL statement and run against the company database and its thrown out many duplicates Bin_XFer_Doc_Number like that one above Bin_XFer_Doc_Number (290).

    They are using GP2013 is sp2 (12.00.1482) and I am going to install service pack (12.00.2104) and hopefully it can resolve this issue.

    I will let you know the result after that.   Really appreciate and many thanks again for your help.

    LC

  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hi Ken

    I don’t think it is feasible to install this service pack 12.00.2104 now because my client had just implemented the GP2013 in April this year.

    Service pack 12.00.2104 is in fact GP2013 R2 and the icons are all different compare to GP2013. In fact GP2013 R2 resolved this issue but not GP2013 service pack 12.00.1482.

    Is there any other service packs in GP2013 that can resolved this issue?

    For this temporary measure; I will go to the Smartlist Designer to create a Smartlist query for them to do an inquiry for Bin Transfer, as well as they can export the data out to Excel.

    By the way, you are the expert and can I ask you another question?

    My client encountered this issue, when he login to GP2013 into company database – he notice these two options disappear in the Inventory module>>Transactions menu>>In-Transit Transfer Entry and on the Inventory Inquiry menu>> In-Transit Transfer History.

    But when he change the company to sample company, he is able to see these 2 options and now he switch back to the company database - now these 2 options appear.

    Don’t understand what causes this issue. Can it be the problem of his laptop or RAM or the cache?

    I am so sorry to trouble you on this issue. Really appreciate all your help and thanking you in advance.

    LC

  • Ken Hubbard Profile Picture
    Ken Hubbard on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hello,

    First from your earlier observation. Yes, having the same doc number with different SEQNUMBR is actually expected.  The true duplicate would be from the following:

    select DOCTYPE, Bin_XFer_Doc_Number, SEQNUMBR  from IV30004  GROUP BY DOCTYPE, Bin_XFer_Doc_Number, SEQNUMBR HAVING COUNT(*) > 1

    From our notes, the issue is resolved specifically in the March 2014 Hotfix (12.00.1570) which is prior to the R2 release.

    To be clear, if you are running into this problem, you can resolve the data condition that causes the error without installing a patch. The patch is just to prevent the problem from occurring in the future.

    If the above select statement does not reveal any records, it may be that you are running into something else which would require picking apart a SQL Trace of the error to understand the data condition. In this case, I would recommend a support incident if you needed further assistance.

    In regards to your additional question, you need to post separate threads for separate issues, but I would look in their Registration to make sure all the proper values are marked as the sample company tends to bypass registration settings where a regular company would not.

    Thank you,

    Ken Hubbard

  • Community Member Profile Picture
    Community Member on at
    RE: Violation of PRIMARY KEY in Inventory Inquiry>>Item Bin Transfer and Bin Transfer

    Hi Ken

    Thank you for your kind response.  After copied your latest SQL script below and run against the company database, no result was returned.

    select DOCTYPE, Bin_XFer_Doc_Number, SEQNUMBR from IV30004 GROUP BY DOCTYPE, Bin_XFer_Doc_Number, SEQNUMBR HAVING COUNT(*) > 1

     Is there a way that I can attached the exported IV30004 data in the Excel worksheet for you to verify it?  It only have 615 rows in it and this data is dated up to 9 Sep 2016.

     I think this is a bug, and will I be charge for this issue support incident?

     Appreciate and thank you for your help all this while.

    LC

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans