Open Forum

Expand all | Collapse all

SQL Error

  • 1.  SQL Error

    Posted 06-13-2018 08:35 AM
      |   view attached
    We have a user that received this error when trying to review a purchase order through the purchase order document inquiry window.  Does anyone know what caused this and a solution.

    The error is attached.


    Mary Kay Merkel
    Synthetic Genomics, Inc.
    La Jolla CA


  • 2.  RE: SQL Error

    Posted 06-13-2018 09:14 AM
    @Mary Kay Merkel,

    The error tells us that GP created a temporary table called ##2463818 and that table has a primary key called PK##2463818 that prevents records with duplicate key values from being inserted into the temporary table. The duplicate key is highlighted. Looks like a PO number + a receipt (?) number.

    We don't use the Purchase Order module (yet) in GP; but I'm surprised that plain old, garden variety document inquiry would produce an error like this.​​

    Do you have GP Power Tools installed? If so, I would turn on Manual Logging just before you do the same document inquiry. I presume that this error occurs every time, yes? Then you'd have the steps leading up to the error.

    If you don't have GP Power Tools, then your IT folks could initiate a SQL Trace just before you do the same document inquiry.

    Those are the analytic steps I would take. I can't tell you what the machinery behind the scenes is trying to do... but there are others here who will know.


    "Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)

  • 3.  RE: SQL Error

    Posted 06-13-2018 09:41 AM
    Steve is exactly correct.The message is telling you that POP Receipt RCT36648 is a duplicate and is not allowed. It is getting queried in the window and generating the error

    There are two initial things to check:

    1. Go to the POP40100 table and verify that the POPRCTNM value stored there as the next RCT number is NOT RCT36648. If it is increment by one to 36649 or 36650. You can also do this in the POP Setup Window.  Keep all the leading 0syou have.
    2. Look for corrupt records in POP10300 (like with no vendor ID) with  POPRCTNM = RCT36648 and delete them. Clear any orphans in POP10310 as well.

    There is also a Purchasing Transactions Checklinks that can be run but this may or may not fix the issue.

    Thaddeus Suter
    Retus, Inc

  • 4.  RE: SQL Error

    Posted 06-18-2018 11:18 AM
    You might want to run checklinks on the module first to see if it will fix the issue before you start deleting things in sql.  Make sure you have a backup and everyone is out of GP before running.

    Austin Adams
    American Innovations
    Austin TX

  • 5.  RE: SQL Error

    Posted 06-14-2018 09:52 AM
    We see this error for POP Receipt transactions where the posting is interrupted and the transaction are in both the work and history files, POP10300/310 and POP30300/310.
    To resolve this you will need to run a SQL query to delete the duplicate records, and also fix the flags in the PO tables POP10100/110 and in the link table POP10500.

    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida / Supervalu
    sunrise FL