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.
- 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
