Error has occurred while moving current year to last year


I've been trying to help a friend who uses Sage and I have hit a wall -- trying to contact Sage support has been very frustrating and futile so I'm turning to the community in hopes that someone here can help me.


When you try to close out the year it asks you to make a backup which is not a problem. After that I presume that Sage is moving the 2017 and that fails with the following error:

An error has occurred while moving current year to last year. As a result, the new fiscal year could not start. Please restore from backup or run Advanced Database Check

 Attempting to run Advanced Database Check leads to a popup saying that the database could not be repaired and to call a 1-800 number which was impossible to get through to anyone on. When I finally did they offered to e-mail some suggestions but sent me an e-mail that mentioned the forums but was otherwise blank.

Eventually, I found the Dbverifier log -- all the verifications Begin and End with no issues except got Historical Date -- Historical Data has the following errors:

# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpysh02 - PrlView
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpysh02 - PrlEdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpysh02 - SystemAdmin
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpys1h02 - PrlView
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpys1h02 - PrlEdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpys1h02 - SystemAdmin
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpys2h02 - PrlView
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpys2h02 - PrlEdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tpys2h02 - SystemAdmin
# HISTORICAL DATA PROBLEM: The historical set in the datadict table does not have the same number of historical years - tGMscH tJEAH tJEH tJEPH tJEPHH tJETH
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tjeh01 - GLEdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tjeh01 - APEdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tjeh01 - AREdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tjeh01 - PrlEdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tjeh01 - InvEdit
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tjeh01 - Common
# HISTORICAL DATA PROBLEM: This historical table exists, but is missing an entry in the simplygrouppriv table - tjeh01 - SystemAdmin
# HISTORICAL DATA PROBLEM: This historical table exists, but tCompOth.nActHistSt is 0 - tJEH01

After doing some Googling I discovered some people with similar problems had solved them by clearing Historical Data. I made a copy of the Sage data and using that copy tried to clear financial history and the result was a popup saying that there is no financial history to clear.

I also attempted to run the Advanced Database Check from outside of Sage -- strangely when I attempt that I am asked for the sysadmin password and despite entering the correct password I keep getting told it is incorrect. This is not a typo -- I have done it a dozen times and can log into Sage with those credentials but the very same credentials do not work for Advanced Database Check run without starting Sage.

I also attempted to export all the records and then start a new company and import them. While that did create a company that had all the customers, vendors, accounts, etc -- the values in all the accounts were zero

So basically it appears that while Sage has been working fine (or at least appears so) there is a problem with the data integrity and the end of year procedure runs a check that is strict enough to fail because of this problem.

I was able to update to version 2018.0 and then to 2018.1 -- in both cases, it modified the database which I thought might fix the problem but in the end, did not.

So what are my options?

Is there a way to run the year-end close while disabling the data check?

Is there a way to dump all the data into something that could then be imported into a new company with everything intact?

Can I manually edit the database and fix or delete the errors?

As far as I can tell there doesn't appear to be any backups. I found a couple but they were all created when they attempted to do the year-end close and I don't think they were doing backups so unless Sage has automatic backups to some non-obvious location simply restoring from backup is not an option.


  • Naldinho said:
    This historical table exists, but is missing an entry in the simplygrouppriv

    Naldinho said:
    Can I manually edit the database and fix or delete the errors?

    I've seen that problem before, and fixed it manually using an ODBC connection.  Sage has since changed which tables are accessible via odbc, and the simplygrouppriv table is one that seems no longer accessible (at least from Access or from LibreOffice, when the data is in multi-user mode).  There is an option somewhere to rebuild security that may fix it.

    Naldinho said:
    I also attempted to run the Advanced Database Check from outside of Sage -- strangely when I attempt that I am asked for the sysadmin password and despite entering the correct password I keep getting told it is incorrect

    Try typing only the first seven characters of the password.  The Sage 50 software silently ignores keystrokes after the 7th character, the database repair tool may not.

    Naldinho said:
    This historical table exists, but tCompOth.nActHistSt is 0 - tJEH01

    This is probably the error that is confusing the year end process.  tJEH01 is the General Ledger transaction table for the year before last.  A problem there could definitely affect / prevent opening a new year.

    Naldinho said:
    I don't think they were doing backups so unless Sage has automatic backups to some non-obvious location

    The data repair will create a backup file, other updates may have created backups in the user folders of various workstations.

    My workstation has backups under:

    (My) Documents\Simply\DbVerifier\Backups

    (My) Documents\Simply Accounting\Backups\CAN2013, CAN2016, CAN2017, etc.

    Naldinho said:
    Is there a way to dump all the data into something that could then be imported into a new company with everything intact?

    No.  Only the 'lists' (accounts, customers, inventory, vendors, employees) static data can be exported, then imported into a new company. 

    Some types of transactions can be imported, but there is no built-in facility to export them in the right format.

    You may have to have the data repaired, esp. the tCompOth.nActHistSt table.  Sage Software may be able to provide repair, or direct you to someone still providing that service.

  • For the Advance database check keep asking for password issue, make sure sysadmin's 3rd party access is on "read and write". (go to Setup, setup user, modify sysadmin) however it does the same as internal function

    For the internal advance database log you have  tCompOth.nActHistSt is 0 which mean your file now have zero historical year  but you have tjeh01 tpys2h02 which are historical year one and two tables.  so data table are not being consistent therefore can not advance.

    not sure if your data have those historical year before.  but most likely the file got partially updated and stuck now.

    Sage or 3rd party data repair should able to fix these type issue. I don't think you can manually do it.

    If you can find a backup that does not have those Historical data problems, you might able to start a new year

  • Thank you for the assistance. That certainly makes sense as the company has been in business for three years so should have three years of financial data but when I tried to clear financial history I was told that there was no financial history to clear as Sage must check the value is 0.

    I'm not a Sage user myself but this seems like it should be fairly easy to fix -- the 0 simply needs to be changed to a 2 and my understanding is that Sage uses a SQL so the database can be edited from SQL assuming Sage hasn't done something specifically to prevent that. I've done something like that a few times to fix corrupted databases on e-commerce sites. 

    This has been very helpful.

  • Jason's answer below explained that the problem is that historical data exists but the value for historical data = 0 when it should be equal to 2. My understanding is that Sage is just an SQL database having switched from Access to MySQL at some point. As such, I'd expect that I could edit the data unless Sage did something specifically to prevent people from doing that.

  • Perhaps Sage knows of some legitimate data repair operations, and could post that information?

    Naldinho said:
    the 0 simply needs to be changed to a 2 and my understanding is that Sage uses a SQL so the database can be edited from SQL

    Yes, the table tCompOth is accessible using an ODBC connection from Microsoft Access or LibreOffice.  If that variable is all that is wrong, you are correct that it is an easy fix.

    BUT I think what Jason is saying is that the wrong value in that field may be just the tip of the iceberg - the value might be stored as a zero when everything goes sideways during a year end conversion. 

    There are a half dozen tables that store information about the data for historical years prior to the (2 years) of data stored in tAccount, and it all has to be right:

    tacthdat, tacthdpt, tacthdrl, tacthinf, tacthrel, tacthrng

    And then the historical information itself isn't just in one table - tJeH01, tJeH02, etc. are just the header records, there are also detail records for each year (transaction amounts, project allocation, allocation by hours, sales tax entered on G/L)

    for example, If one year's detail accounting entries were moved, but the header records weren't because of a crash or power failure, the data is more or less shredded since nothing matches up correctly.   The database repair tool is pretty good, but it's an automated, brute force tool that cannot always fix things correctly if they're really wrecked.

    Because the data seems to open, and seems to work properly, the damage is probably not too serious.  I would work with the OLDEST, least touched backup or copy available, and run the data repair tool on a copy of that, rather than first converting to a newer software version.