Sage 100

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

HI Tom

   I have a client that is on premium and has about 10 companies.   They had an issue where transactions were missing from the GL detail transaction report causing the reports to show out of balance.  The transactions were shown in account inquiry.    They are on 2019 so I was limited in being able to bring in Sage on it.    Along the way someone and I wish I could remember who told me that in Premium issues with the SQL logs can cause issues like this.   In order to do some transactional testing, I backed up the company and then copied it to another company.   After I did this, the issue went away.  My only thought is that the backup cleared some of the SQL logs.   Still no explaination.   Nathan Shaw replied to my email after if was solved and even he had no insight to the issue.     Worth a shot & good luck! 

Bob Osborn , MCSE Sr. Consultant, Sage 100  e: BobO@dsdinc.com p: 800-627-9032 x 180 s: Sage100Support@dsdinc.com

    

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

Bob,  

When you say you backed it up and restored to another company, are you referring to a different company code?

We have a production environment and a test environment.  We backed up from the production environment and restored to the test environment but using the same company code and sql database name..

HI Tom

   I have a client that is on premium and has about 10 companies.   They had an issue where transactions were missing from the GL detail transaction report causing the reports to show out of balance.  The transactions were shown in account inquiry.    They are on 2019 so I was limited in being able to bring in Sage on it.    Along the way someone and I wish I could remember who told me that in Premium issues with the SQL logs can cause issues like this.   In order to do some transactional testing, I backed up the company and then copied it to another company.   After I did this, the issue went away.  My only thought is that the backup cleared some of the SQL logs.   Still no explaination.   Nathan Shaw replied to my email after if was solved and even he had no insight to the issue.     Worth a shot & good luck! 

Bob Osborn , MCSE Sr. Consultant, Sage 100  e: BobO@dsdinc.com p: 800-627-9032 x 180 s: Sage100Support@dsdinc.com

    

Hi Tom

  I backed up the database out of an abundance of paranoia..    This was a few months back, so I am not 100% sure of the order that I went in.   The two things I know for sure is that the copied company did not have the issue and that the live company issue went away after it was backed up.     In both cases I believe that the SQL logs get "cleaned up".     

Bob Osborn , MCSE Sr. Consultant, Sage 100  e: BobO@dsdinc.com p: 800-627-9032 x 180 s: Sage100Support@dsdinc.com

    

Bob,  

When you say you backed it up and restored to another company, are you referring to a different company code?

We have a production environment and a test environment.  We backed up from the production environment and restored to the test environment but using the same company code and sql database name..

HI Tom

   I have a client that is on premium and has about 10 companies.   They had an issue where transactions were missing from the GL detail transaction report causing the reports to show out of balance.  The transactions were shown in account inquiry.    They are on 2019 so I was limited in being able to bring in Sage on it.    Along the way someone and I wish I could remember who told me that in Premium issues with the SQL logs can cause issues like this.   In order to do some transactional testing, I backed up the company and then copied it to another company.   After I did this, the issue went away.  My only thought is that the backup cleared some of the SQL logs.   Still no explaination.   Nathan Shaw replied to my email after if was solved and even he had no insight to the issue.     Worth a shot & good luck! 

Bob Osborn , MCSE Sr. Consultant, Sage 100  e: BobO@dsdinc.com p: 800-627-9032 x 180 s: Sage100Support@dsdinc.com

    

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

The odd thing is that the differences are accounts simply disappearing, not changed balances in the accounts.

In SQL where do the work tables get created?  I previewed the report and I could find the work tables anywhere like we do in the PVX environment.

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

Work tables are in tempdb.

The odd thing is that the differences are accounts simply disappearing, not changed balances in the accounts.

In SQL where do the work tables get created?  I previewed the report and I could find the work tables anywhere like we do in the PVX environment.

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

Thanks Phil

Work tables are in tempdb.

The odd thing is that the differences are accounts simply disappearing, not changed balances in the accounts.

In SQL where do the work tables get created?  I previewed the report and I could find the work tables anywhere like we do in the PVX environment.

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

Work tables are in tempdb.

The odd thing is that the differences are accounts simply disappearing, not changed balances in the accounts.

In SQL where do the work tables get created?  I previewed the report and I could find the work tables anywhere like we do in the PVX environment.

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

More interesting info.

If you use the explorer function to look at Accounts, the accounts that are randomly disappearing do not show up.

Looking into the SQL database itself we can see that the accounts in question are more newly created accounts based on looking at the accountkey.

Work tables are in tempdb.

The odd thing is that the differences are accounts simply disappearing, not changed balances in the accounts.

In SQL where do the work tables get created?  I previewed the report and I could find the work tables anywhere like we do in the PVX environment.

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.

Look in SQL at the Columns under dbo.GL_Account and see if the fields are listed and look at the data type for each field to be sure it looks correct.  For example, AccountKey (PK,varchar(9), not null) 

More interesting info.

If you use the explorer function to look at Accounts, the accounts that are randomly disappearing do not show up.

Looking into the SQL database itself we can see that the accounts in question are more newly created accounts based on looking at the accountkey.

Work tables are in tempdb.

The odd thing is that the differences are accounts simply disappearing, not changed balances in the accounts.

In SQL where do the work tables get created?  I previewed the report and I could find the work tables anywhere like we do in the PVX environment.

In that test company, reinitialize the GL_PeriodPostingHistory table and run the recalculate again, then compare results.  My guess is that the report filter is hiding some bad data in the table, and the recalculate "fixed" some of it.  The recalculate utility doesn't delete bad rows, only recalculates good rows, which is why you need the reinit.

If you want to do the verification in SQL, look at the GL_PeriodPostingHistory data, compared with a sum of all DR-CR transactions, from the beginning of time, for each account.