Error generating the report of the General ledger trial balance in GP 2018.

(0) Share

Report

Posted on by Juan Bonilla Hn

When I am generating the report it generates the error. I have already tried checking links. but always the same mistake.

5756.error.png

Categories:

Microsoft Dynamics GP

All responses (7)

Answers (1)

Juan Bonilla Hn 55 on at

Like (0)

Report

RE: Error generating the report of the General ledger trial balance in GP 2018.

Hi Beat,

That's how it is. something happened on the tables and they were corrupted. I already did what Brandon told me and everything went well.

I thank you for your attention.

Regards.
Juan Bonilla Hn 55 on at

Like (0)

Report

RE: Error generating the report of the General ledger trial balance in GP 2018.

Hello Brandon,

I already made the indications you gave me in the test database. everything was a success. Now I will do the same in the production environment.

Thank you very much for your indications.

Regards.
Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: Error generating the report of the General ledger trial balance in GP 2018.

Hi Juan,

Then it is likely a data corruption between GL20000 & GL30000 as Brandon suggested to check..

Some reports might work, some not, depending on what data is aggregated in the report..

The fact that it started to happen after the service pack apply might be a sign, but not necessarily the root cause.
Juan Bonilla Hn 55 on at

Like (0)

Report

RE: Error generating the report of the General ledger trial balance in GP 2018.

Hello Brandon,

Thanks for answering. I am going to carry out the procedure that you indicate. If I have doubts, any problem or success in the solution, I will indicate it to you.

stay tuned
Juan Bonilla Hn 55 on at

Like (0)

Report

RE: Error generating the report of the General ledger trial balance in GP 2018.

Hi Beat,

thanks for answering. I tell you that the error is generated in both ways, when I use the original GP report and the modified one, in both cases the same error is generated.

I tell you that this was generated after updating GP from version 2016 to version 2018 with the service pack of February 2021. Before this everything was fine.

The interesting thing is that it only happens with all the summary and detail trial balance reports. all other GP reports are fine.

Do you have another idea of what could be happening?
Verified answer

Hokuminaria 2,950 on at

Like (1)

Report
RE: Error generating the report of the General ledger trial balance in GP 2018.

Hello Juan,

This error can be tricky and will likely need a support case to get addressed.

My assumption is our Identity Column is incorrect on one of GL tables and it is why it is erroring when attempting to put value into the temp table. My initial thoughts would be that it is incorrect on the GL20000 or GL30000 depending on if you are running the report for an Open or Historical year.

You can try the following to determine if this corrects the error.

I recommend testing this in a test company with a copy of live data to ensure that you receive the results you are looking for:

KB 871973 How to set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, or SQL Server 2005

https://support.microsoft.com/en-us/help/871973/set-up-a-test-company-that-has-a-copy-of-live-company-data-for-microsoft-dynamics-gp-by-using-microsoft-sql-server

NOTE: Please make sure that you have a restorable backup prior to performing any troubleshooting steps.

Rebuild the GL20000 or GL30000 using PSTL.

First determine if you are running the TB report for an Open or Historical Year so we know what table to rebuild.

GL20000 = Open Year.

GL30000 = History Year.

Count the number of Record in the table you are going to rebuild so we can verify the same number of records exist after the rebuild.

select count(*) from GL20000

Script out the Triggers that are on the table you will be rebuilding as these will need to be created.

Open SQL Server Management Studio and log in to the Instance.

In SSMS, Expand the Company Database.

Expand Tables.

Locate the table we are rebuilding and Expand it.

Expand the Triggers Folder.

Right click on the Trigger and select Script Trigger As > Create To > New Query Editor.

This will create a SQL Script in a new query.

Save this to a safe location.

Repeat for any other triggers on the table we will be rebuilding.

Use PSTL to rebuild the table.

Open PSTL by clicking the COG icon on the bottom left.

Click PSTL Main Setup next.

Mark the Toolkit Radial button and click Next.

In the Toolkit window, mark the ‘Recreate SQL Objects’ radial button and click Next.

In the System Diagnostics window, select the following:

Series = General Ledger

Table = GL20000 or GL30000 depending on if we are running the report for Open or History.

Maintenance Options to select.

Recreate Selected Table

Recreate data for selected Table(s)

Click Perform Selected Maintenance.

There will be 3 pop ups informing you to make a backup and triggers on the table.

Once the backup is verified, click ok on the first two pop ups and recreate on the last one.

Let the process finish and close the window.

Run the Trigger Scripts we scripted out against the company database to recreate them.

Count the records in the table to verify the same number of records are there was before.

select count(*) from GL20000

Determine if the error still occurs.

If the error still occurs, we will likely need a support case to capture logs to see what is occurring.

I hope this helps!

Thank you!

Microsoft Support Engineer | Brandon Jarrett
Suggested answer

Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: Error generating the report of the General ledger trial balance in GP 2018.

Hi Juan,

is this a modified version of the GP report ? this looks like a temp table used during the report generation (the ## indicates usually SQL temp tables).

The IDENTITY_INSERT seems to be related to a primary key violation that is defined, which means the process tries to insert twice the same data into the temp table, which is not allowed.

Some logic of this report seem to have been altered.

Can you check the GP Alternate Forms & Reports security to make sure it's using the original GP report ?