Error message when you try to post a batch in Microsoft Dynamics GP: "The stored procedure glpPostBatch returned the following results: DBMS: 0, Great Plains: 20286"

Applies to: Dynamics GP 2010Dynamics GP 2013Microsoft Dynamics GP 2015

Symptoms


When you try to post a batch in Microsoft Dynamics GP, you receive the following error message:
The stored procedure glpPostBatch returned the following results: DBMS: 0, Great Plains: 20286
glpPostBatch. Select on Batch_Headers failed.

Cause


This problem occurs for one of the following reasons:

  • Posting Definitions Master table (SY00500) is corrupted
  • Missing or damaged stored procedure for 'dbo.glpPostBatch'
  • Missing trigger on the SY00500 table after rebuilding it
  • Stuck record in the SY00800/SY00801 activity tables in Dynamics database

Resolution


Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

Microsoft Dynamics GP

To resolve this problem, re-create the Posting Definitions Master table (SY00500). To do this, you can use either of two options. The steps in Option 1 delete all batch records that are in the Posting Definitions Master table. The steps in Option 2 keep the existing batch records by using the Professional Services Tools Library (PSTL) tool. This tool will help you re-create the Posting Definition Master table and re-create the stored procedures and the auto procedures without losing data.

Option 1: Use file maintenance

  1. Have all users exit Microsoft Dynamics GP.
  2.  On the Microsoft Dynamics GP menu, point to Maintenance, and then click SQL.
  3. In the Database list, select the company database.
  4. In the table list, click Posting Definitions Master. (which is the SY00500 table)
  5. Click to select the following check boxes:
    • Recompile
    • Update Statistics
    • Drop Table
    • Create Table
    • Drop Auto Procedure
    • Create Auto Procedure
  6. Click Process.
  7. Re-create the batches for each module in which you have unposted batches. To do this, follow the steps for each module.
    • General Ledger
      1. On the Microsoft Dynamics GP menu, point to Tools, point to Utilities, point to Financial, and then click Reconcile.
      2. In the Reconcile Financial Information dialog box, click to select the Batches check box, and then click Reconcile.
      3. When you are prompted to print the Error Log report, click Cancel.
    • Payables Management
      1. On the Microsoft Dynamics GP menu, point to Maintenance, and then click Check Links.
      2. In the Series list, click
        Purchasing.

        Note If you do not use the Multicurrency module, go to step 7.
      3. In the Logical Tables list, select
        Payables History Logical Files, and then click
        Insert.
      4. Click OK.
      5. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
      6. Close the File Maintenance Error Report dialog box.
      7. On the Microsoft Dynamics GP menu, point to
        Maintenance, and then click Check Links.
      8. In the Series list, click
        Purchasing.
      9. In the Logical Tables list, select
        Payables Transaction Logical File, and then click
        Insert.
      10. Click OK.
      11. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
    • Purchase Order Processing
      1. On the File menu, point to
        Maintenance, and then click Check Links.
      2. In the Series list, click
        Purchasing.
      3. In the Logical Tables list, select
        Purchasing Transactions, and then click
        Insert.
      4. Click OK.
      5. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
    • Receivables Management
      1. On the Microsoft Dynamics GP menu, point to
        Maintenance, and then click Check Links.
      2. In the Series list, click
        Sales.
      3. In the Logical Tables list, select
        Receivables Open Transaction Files, and then click
        Insert.
      4. Click OK.
      5. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
    • Invoicing
      1. On the Microsoft Dynamics GP menu, point to
        Maintenance, and then click Check Links.
      2. In the Series list, click
        Sales.
      3. In the Logical Tables list, select
        Invoice Work, and then click
        Insert.
      4. Click OK.
      5. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
    • Sales Order Processing
      1. On the Microsoft Dynamics GP menu, point to
        Maintenance, and then click Check Links.
      2. In the Series list, click
        Sales.
      3. In the Logical Tables list, select
        Sales Work, and then click Insert.
      4. Click OK.
      5. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
    • Inventory
      1. On the Microsoft Dynamics GP menu, point to
        Maintenance, and then click Check Links.
      2. In the Series list, click
        Inventory.
      3. In the Logical Tables list, select
        Inventory Transaction Work, and then click
        Insert.
      4. Click OK.
      5. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
      6. On the Transactions menu, point to
        Inventory, and then click Batches.
      7. Click the next record button to display the first inventory batch.
      8. Click to select the Post to General Ledger check box.
      9. Repeat steps 7 and 8 for each batch.
    • Bill of Materials
      1. On the Microsoft Dynamics GP menu, point to
        Maintenance, and then click Check Links.
      2. In the Series list, click
        Inventory.
      3. In the Logical Tables list, select
        Bill of Materials Transactions, and then click
        Insert.
      4. Click OK.
      5. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.
    • Project Accounting
      1. On the Microsoft Dynamics GP menu, point to
        Maintenance, and then click PA Check Links.
      2. In the Logical Tables list, click the first table that is listed and that corresponds to a Project Accounting transaction type that you use. Then, click Insert.

        The following table lists the transaction types and the tables that are available in Project Accounting.
        Transaction type Table
        Timesheet PA Timesheet Transactions
        Equipment Log PA Equipment Log Transactions
        Miscellaneous Log PA Miscellaneous Log
        Inventory Transfer PA Inventory Transfer Transactions
        Purchasing Transactions PA Purchasing Transactions
        Revenue Recognition PA Revenue Recog Transactions
        Employee Expense PA Employee Expense Transactions
        Billing Batches PA Billing Transactions
      3. Repeat step 2 by selecting the next applicable table that is listed in that step. Repeat step 2 as many times as required to insert all the applicable tables. Then, click OK.
      4. In the Report Destination dialog box, click to select the Screen check box, and then click
        OK.

Option 2: Use the free toolkit from Professional Services Tools Library

  1. Obtain the Professional Services Tools Library (PSTL) tool for Microsoft Dynamics GP 2010 or for Microsoft Dynamics GP 10.0.

    For more information about the Professional Services Tools Library, use one of the following options:

    Customers:
    For more information about PSTL, contact your partner of record. If you do not have a partner of record, visit the following web site to identify a partner: Microsoft Pinpoint

    Partners:
    For more information about PSTL, visit the following web site:

    https://mbs.microsoft.com/partnersource/downloads/releases/noam_pstl.htm
  2. Install the PSTL tool.
  3. When the installation is complete, start Microsoft Dynamics GP.
  4. When you are prompted to include a new code, click
    Yes.
  5. Log in to Microsoft Dynamics GP as the system administrator (sa).
  6. To add the PSTL tool to the shortcut bar, click Add, click Other Window, expand
    Technical Service Tools, expand Project, click Professional Tools Library, click Add, and then click Done.
  7. On the shortcut bar, click Professional Service Tools.

    Note You must wait for the SQL objects to build.
  8. When you receive the prompt to enter registration keys, click Cancel.

    Note You do not have to have registration keys to use Toolkit.
  9. Click Toolkit. The, click
    Next.
  10. Under Toolkit Options, click
    Recreate SQL Objects, and then click Next.
  11. In the Series field, click
    Company.
  12. In the Table field, click
    SY00500.
  13. Under Maintenance Options, click to select the
    Recreate Selected Table check box, and then click to select the
    Recreate data for selected table(s) check box.
  14. Click Perform Selected Maintenance.
  15. Click OK when you receive the following messages:
    Table will be recreated with data.
    SY00500 table will be recreated with data. Verify a backup has been made before proceeding.
    SY00500 has been recreated.
Additionally, you may have to re-create a trigger that is advised by Toolkit.  (see option 3).

 

Option 3:  After rebuilding the SY00500 table, check to see if the 'glpBatchHeadersDeletetrigger' exists on the table:   If not, script it out from a working install to recreate it. 

sp_helptrigger sy00500

 

Option 4:  Have all users log completely out of Dynamics GP and clear out the activity tables: These tables should be empty when all users are out.

SELECT * FROM DYNAMICS..ACTIVITY --shows users currently logged into GP

SELECT * FROM DYNAMICS..SY00800

SELECT * FROM DYNAMICS..SY00801

SELECT * FROM TEMPDB..DEX_LOCK

SELECT * FROM TEMPDB..DEX_SESSION

 

DELETE DYNAMICS..ACTIVITY

DELETE DYNAMICS..SY00800

DELETE DYNAMICS..SY00801

DELETE TEMPDB..DEX_LOCK

DELETE TEMPDB..DEX_SESSION