Hello again everyone. In todays "IN THE WILD" we will talk about something that is popping up much more frequently with respect to HRMS 2015 SQL PR. The topic is how and when to take a database dump and do a database load and what the benefits of this process are. Let’s start first with practical application and a real world scenario we are seeing from time to time that justifies this process.
Users on 2014 (10.4x) are starting to upgrade to 2015 (10.5x) to take advantage of the newer more stable version of HRMS and enjoy the new sleeker look of the loading screen. On upgrade some users have reported that when they run calculate payroll they are able to go through it just like normal (and sometimes faster), but then when they get to print/post they get a large error screen with a couple lines citing databases or views being missing. The error screen itself has varied so I would focus too much on the wording, what i would focus on is if you just upgraded to 2015 and everything worked great until you went to print post. In this scenario your best and almost certain solution is to take a database dump , followed by a database load.
What is a dump.
It's a process inherent to the accpacc (sage 300) back end of our payroll that takes the contents of the SQL database and puts it into a flat file, the act of creating this flat file corrects some exotic data and schema conditions. Once the dump is created, you can then reload that data back into the SQL tables and clean up those irregular and hard to find issues with no risk of damage to the integrity of the data.
WOW!!! how do I do this.
1. Well first with all things database level we are going to very strongly recommend that you make a backup of all SQL tables that you are going to be working with.
2. Log into the ACCPACC back end (sage payroll/ sage 300) PRO TIP-- if you have multiple companies, do this all from a company other than the one you are working on at that moment; so let’s say employer AAA is giving you the hard time, log into employer BBB.
3. Under administrative services, choose database dump. Make sure to choose the correct company to make a dump file of and give it a name that will stand out so you don't accidentally mix it up with another file. I like to use the convention First initial, last initial, company id, date and then then deliberately misspelled world DUUUUMP. example: JCAAA03152015DUUUMP.
this process can take anywhere from a minute to ten so expect some grind time.
4. once that is complete log into a company that is NOT the one in question (that’s because you can’t upload to a database you are using). back to administrative services, choose database load. also make sure to correctly choose the employer/company you are restoring to as well as the correct dump file.
this process will take about 50% then then step 3. Just expect it.
There is of course the possibility that you just have the one employer. Database load and database dump are available outside of the product from the programs/start menu directly. The interface is not as easy to navigate but not impossible. If needed support can assist further with that process.
ARE THERE ANY OTHER PROBLEMS THAT JUSTIFY THIS PROCESS?
Yes. But each of them are random and has to do with database integrity. One example where I had to do this was when I was trying to pull transaction history for an employee, checks within a given range kept returning strange errors. I isolated the problem down to a specific earnded for that employee for a specific range. I then compared that entry to all the other entries that did not break. There was no substantive difference between them (upchkd). When I tried to manipulate the record directly in SQL it got an exotic SQL error and that was my clue that I needed to take a dump and reload.
Another circumstance was found when running open payroll. The process was halting when attempt to insert a deduction on an employee’s record (upempd). Another exotic error without a clear sign of solution. I went into UPEMPT to investigate and manually manipulate the records and sure enough, I got another exotic SQL error I did not recognize. . Once again time to dump and reload.
WHAT ARE THE RISKS OF THIS PROCESS?
We don't know. But we do know it's a good idea to back up your SQL data before proceeding.
I hope this article has been helpful but more over I hope someone actually reads this. I've begged for participation and engagement from the community but I haven’t gotten anything; No likes or comments just salience. That make me a sad panda. If anyone has any ideas on topics you would like me to write about, I'm game. Do you want to know how to write certain types of SQL scripts within context of HRMS ? Maybe recap TWD or Saul? Do you want to know how to build complex compound expressions for HRMS benefits and which ones can't possibly interact with ESS if you decide to try to integrate them? I'm your Guy.
