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.


Parents
  • 0

    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

  • 0 in reply to Jason.

    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.

Reply
  • 0 in reply to Jason.

    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.

Children
  • 0 in reply to Naldinho

    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.  

  • 0 in reply to RandyW

    Not my company but I didn't see any backups and when I asked about backups I didn't get a clear answer but my impression is that none have been made -- there was likely one at last year at close but I couldn't find even that one and going back a whole year is not an option. I'll look again but I'm working on the assumption that the only backups that exist were made in the last few days and all have the issue.

    I considered that the data might be damaged but as you said it can't be too bad -- they are still using it to run the business but just currently dating everything Dec 31. If after changing the value to 2 if the close doesn't work I figured I could use Clear Financial History and that would just dump all the tables for the previous two years and after that, I should be able to do the close. I'd have to make a copy of the data first so they would have the previous year's data should they need to access it as one company file and then proceed with the second company file as the active company. This isn't ideal as they would lose the ability to do comparisons but I'm just going to present them with all the options on Monday

  • 0 in reply to Naldinho

    Yeah I do not think simply change 1 value will fix your issue here.

    you can check 

    C:\Program Files (x86)\Sage 50 Accountant Edition Version 2018Version\Manuals

    there is a datadict.pdf  and there are a lot of tables to check, you can also download SDK to get Table relation pdf that might help as well

    to repair this file you might also need fully access to those table too.

    as for backups

    you can check support article 12613 there are few places you might able to find a backup.

    Good luck

  • 0 in reply to Naldinho

    Naldinho said:
    Clear Financial History and that would just dump all the tables for the previous two years and after that, I should be able to do the close.

    Technically.. DROP, not DUMP, but yes, that work likely work, provided the current year data is complete and correct. 

    I would try:

    set the variable to the correct number of history tables (i.e. JEH01 / JEAH01, JEH02 / JEAH02, etc.)

    Make a backup

    Restore / move / copy to another location and run the data rebuild tool.

    Make another backup or copy

    Evaluate the copy.

    It may be unnecessary to chop off the older years, the damage may be minor.  Or they could then be hopelessly mangled, with various parts gouged out off by the data repair tool and other bits grafted where they don't belong, like some horrible transporter malfunction.