Dynamics GP – Year End Closing – Query Timeout Expired Error | “The Stored Procedure glYearEndCloseMain”

If you are performing the year end closing procedure and encountering the error ” Query Timeout Expired” specifically at step 3 out 7, then an error message popped up ” The stored procedure glYearEndCloseMain returned the following results DBMS … “, you need to know that the GL year end closing procedure has failed as it partially closed the year.

Year End Closing - Query Timeout Expired Error | "The Stored Procedure glYearEndCloseMain"

Issue Diagnosis:

When closing the year, Dynamics GP goes through seven steps precisely in order to reconcile GL balances, check returned accounts, account types … etc and close the year.

Step 3 calls stored procedure “glYearEndCloseMain” which primarily moves records from GL20000 to GL30000, create BBF records. Here is the SQL statement which is causing the system to hang:

BEGIN
    DECLARE @stored_proc_name CHAR(26);
    DECLARE @retstat INT;
    DECLARE @param23 NUMERIC(19, 5);
    DECLARE @param24 INT;
    DECLARE @param25 INT;
    SET NOCOUNT ON;
    SELECT @stored_proc_name = ‘TWO.dbo.glYearEndCloseMain’;
    EXEC @retstat = @stored_proc_name 2017,
                                      2018,
                                      1,
                                      ‘Z-US$’,
                                      ,
                                      0,
                                      ‘AVERAGE’,
                                      ‘CLOSE’,
                                      639100,
                                      ‘GLTRX00014007’,
                                      ‘DYNSA’,
                                      ‘##0545413’,
                                      ‘##0545449’,
                                      ‘##0705413’,
                                      1460,
                                      0,
                                      0,
                                      639100,
                                      639100,
                                      0,
                                      ‘2017.01.01’,
                                      ‘2017.12.31’,
                                      @param23 OUT,
                                      @param24 OUT,
                                      @param25 OUT;
    SELECT @retstat,
           @param23,
           @param24,
           @param25;
    SET NOCOUNT ON;
END;

Resolution 
The very initial step to ensure that you restore Dynamics GP databases since in such case the GL tables would be partially corrupted due to having “partial year end closing”

The next step is to check the “Auto Growth – Maximum Size ” property on the database server for the GP database. The case occurred due to having insufficient database size restricted by the maximum size.

 

Leave a Reply

Your email address will not be published. Required fields are marked *