Should you backup your database before processing payroll?

A client called yesterday and were in a bit of a tizzy. They processed payroll last month and in the middle of things GP crashed. They said a few cuss words, logged back into GP then continued processing. When they went to do their period end reports they were a little taken back because gross wages was off by $13,000,000. I had to write that out just to see how many zero's that is.
A few questions came out of the CFO's mouth such as:

• How could this be?
• Did they really send a check out to an employee for that amount?
• Is our bank reconciliation off?
• Is our GL amounts off?

We discovered an employee with the below information:

My first question was - "Are you sure this employee is still in the country because if I received a check like that, I'm heading straight to Samoa for an indefinite period of time."

They chuckled a bit but I don't think they found my comments to helpful.

The payrun that caused this was of course two weeks ago so restoring from a backup was not really an option. This made me pull out my SQL skills and go to work. I used tech doc 948268 to resolve the issue but here are the basic steps:

1. Backup Company databases
2. Make sure you backed up the correct company database
3. Delete the payrun in question from the following tables using these scripts. Replace XX with the correct Audit trail code. delete UPR30100 where AUCTRLCD='XX' delete UPR30200 where AUCTRLCD='XX' delete UPR30300 where AUCTRLCD='XX' delete UPR30400 where AUCTRLCD='XX' delete UPR30401 where AUCTRLCD='XX'
4. Delete the employee's summary information by using this script, replacing YY with the employee ID and ZZZZ with the year in question. delete UPR30301 where EMPLOYID = 'YY' and YEAR1 = 'ZZZZ'
5. Run Reconcile in payroll - Point to Tools on the Microsoft Dynamics GP menu, point to Utilities, point to Payroll, and then click Reconcile.
6. Deal with bank reconciliation module and GL as necessary

I ended up deleting one table that had 450,000 rows duplicated for one transaction. Thus the 13 million dollar employee card.

Now the real question is should a backup be done before processing a payroll batch? The answer is a resounding YYYYYYYEEEEEEESSSSSSS!!!!! Does that mean everytime? Let me be clear. Everytime you process payroll you are at risk of having your payroll module blow up. The above was a fairly easy fix compared to what can happen. I wish I could be more upbeat about how great GP is and how solid payroll is as a module.

Another thought. We have a couple of clients that all they do is payroll. They process thousands of transactions a week and in any given day they have several people posting payroll batches. To back up their database takes 10 to 20 mins. To do a backup each time is not really feasible. So after explaining the situation clearly they have decided to risk a days worth of work for irregular event of having the payroll module messed up with a posting interruption.

Talking with Mike from our office about this event and he told me how he fired someone when he was a controller when they processed payroll without doing a backup. The one and only time they didn't have a backup resulted in heartache, gnashing of teeth, Mike flying off the top ropes with a 10 key....you get the drift.

Here is a link to backing up your GP system.

Please consider making a backup before processing your payroll. One time in a thousand you'll actually need it but you'll thank your lucky stars when you do.

It Pays to Know a SQL Expert, Especially if you are not!

As consultants, we all have aspects of our work that are more frustrating than others. Migration of data is one of those tasks that I prefer to do alone...I tend to swear a lot and it isn't pretty! Last week I had a payroll migration that was one of the most hairy to date and I have been doing this for years.

It started out very vanilla. After all, it doesn't matter the quantity of data...it is all about the quality, right? I only needed current year employee payroll transactions. My methodology is very tried and true. I Download the data into Excel, clean it up, save it, map it to Integration Manager and after a few hit and miss tries...the data has been successfully integrated. I can move on to something more exciting.

My client runs about 2,500 payroll checks a week and there is a massive amount of employee turnover. In the legacy system (which is housed in a separate facility from the new implementation) employees were not inactivated and, after several years, there were literally hundreds of thousands of employee master records.

As in Dynamics GP, the data that I needed was in several different tables, and had to be exported. No problem I think, and dump out the historical payroll transactions via SQL table export. I will do the same thing with the entire employee master record table, Do a VLook-up to parse out the employees that haven't been paid this year...and there you go... I have what I need to import into my new GP database.

Who knew there was a limit to how much data Excel could handle. Yes, I know, there is a max. amount of rows - and Excel 2007 can hold a lot! Apparently, not enough for my employee master and state tax file. After several attempts (and a lot of swearing) I have the data in separate workbooks. Now to get rid of the employees I do not need. Not so easy. The calculation speed was painfully slow and while I could import data to all 1M rows, Excel couldn't run the formula due to memory constraints (so I am told.) 12 hours later I am completely frustrated and at my witts end.

This is where the story gets good. My fairy godmother (a.k.a. my boss) tells me to quit pulling my hair out and call Tom Celvi. He is a SQL wizard! I am a bean-counter and application consultant. I have always thought of SQL as something to be respected and a bit feared. After all, it is the "house" for my GP data.

Silly me! There is a whole other world out there that I owe myself to learn more about. Tom has shown me that SQL is a very powerful tool and can be used for other things besides housing my precious GP databases.

Tom created a database within the clients SQL 2008 environment, imported all my raw, ugly data and through a series events that I can only explain as pure alchemy...he managed to move that cleaned up data into my GP database and I had my data in all the correct payroll tables along with only the 12,000 current employees!

This took Tom 10 hours in total and I bet he didn't swear once! I was then able to successfully integrate the current year payroll transactions via Integration Manager and my my client is live and processing payroll.

Under lessons learned:

1. SQL doesn't have to be feared, it can be your friend (but should be respected)


2. Quality over Quantity is over rated - strive for both because Excel does have its limits.


3. hang out with a SQL guru, it will save on hair dye

2008 Payroll year end close update Dynamics 10.0

Bear with me a moment while I grumble and I'll get to YEC for 10.0.

Great Plains isn't what it used to be. That's both good and bad I suppose.

It is good that all workstations should be on the same SP version to be able to log into a company. This saves many significant issues popping up as most are well aware. Workstations used to always be out of sync and a lot of the time the resolution would be "Apply this patch like all the rest of the workstations. etc."

However it is frustrating when you only need to apply the year end update for payroll. In times past you could put the cnk file only on the payroll persons workstation and they'd be good to go. It's a major effort now to apply any service pack. It almost feels like people are being pushed into a Citrix/Hosted or Terminal Server model. There are major advantages of only having to apply SP's once at a central location. I know you can have the SP automatically apply the next time a user logs into the system but that is mostly just a huge pain none the less.

Ok, 10.0 year end update. Here are a few things I've come across while going through the year end update. Most are pretty user specific but here they are none the less.

1. All people must be out of the system. Sounds like a no brainer but we spent several mins troubleshooting why the system tables wouldn't update then found some people had snuck into the system. (Hopefully Mike's head doesn't hurt too bad from hitting it repeatedly on the desk in front of him.)


2. To install the update make sure the installation of GP is at least the first recommended release of 10.0. Had one client on 10.0.465 which is a beta release of 10.0. Who knows how they got that one installed. Kept getting this erorr when trying to apply the update. Applied 10.0 Feature pack 1 which includes SP3 (1.5 gb download so don't do this with a 56k modem), applied the update and they were good to go.
3. You can run the update from a client workstation. Don't need to be at the server. Kind of counter intuitive but it works. After corrupting GP on the server during resolution of #1 we got the update to go on a client workstation. (Thanks to Jamie Nelson on this one).
4. Plan for several minutes per database. I've timed several updates and it seems like it runs around the 20 min range per database. This is true when the databases are pre SP3. Not sure if it's quicker if SP3 is already applied. (Anyone know this?)
5. You must run the year end wage file before applying 2009 tax tables. Duh.
6. You can't run a 2009 payroll before running the year end wage file. Duh again. ha.
7. User gets kicked out of GP when running payroll reports after applying update. Recreated reports.dic as it was corrupted in the process. So....make sure you backup reports.dic, forms.dic, dynamics and company databases etc. The process looks like it actually exports then imports the reports as part of the update so that could easily cause issues.

Here's a few other Year end update comments from Christina Phillips on her blog.

Victoria Yudin still has a great post on Year end close resources.

2009 payroll tax update for Dynamics GP 8.0

For those slackers still on Dynamics Great Plains 8.0 and are using payroll here is a procedure to apply the 2009 tax tables before you run your first payroll of the year. You probably don't need to worry about the tax update for 2008 as no form changes have been made. You should do the payroll year end procedure (tech doc # 850663) as usual but when it comes to applying the 2009 tax update you will have to do something like this.

Here is the 2009 payroll tax update script. It is for 10.0 but I tested on my 9.0 system and it completed successfully. I would recommend this procedure for applying the tax tables for 8.0:

1. Verify current tax numbers that are currently setup in payroll tax tables. Tools>>setup>>system>>payroll tax. Also note what date is listed for Last Tax Update (use in step 4)
2. Back up the Dynamics and company database
3. Run the script in SQL
4. Look at same tables as step 1 and verify changes have been made. Also verify last tax update is now listed as 12/19/2008
5. Run a payroll in a test company to verify the numbers are correct
6. Run payroll in production company and verify numbers are correct

Hope that helps you remaining 8.0 folks. Need to consider upgrading soon.

Payroll year end for 8.0

Got a funny email from MBS last week. For several months we've been complaining to MBS about not supporting Dynamics 8.0 for payroll year end. Seems wrong for Microsoft to say 8.0 is a supported version........but no payroll year end update will be provided. Had a few clients scrambling to upgrade (they should have done this already but haven't). Deep down inside I was crossing my fingers that MBS was going to break down and provide the update anyways.

Well I get an email from Terry Heley at MBS (Great payroll resource to be in contact with) saying the payroll update has been released for Dynamics 9.0 and 10.0 and there has been no changes from last year to the payroll forms.

What does that mean? Well those on GP 8.0 can probably squeak by without upgrading (at least untill October 2009 when 8.0 is MBS un-support-able) as no year end file is needed to be run other than for a few scenarios. You can also manually update the tax tables for 2009 with a SQL script.

Moral of the story for customers - Upgrade when recommended by your VAR. Last minute upgrades are often bloody and painful especially if your system has any 3rd parties or customizations. You also run the risk of Microsoft not supporting critical business processes such as payroll if you wait too long.

Moral of the story for Me - Crossing your fingers does work......sometimes.

Payroll ACH file deletion

Had a customer call today that had lost a build for 800 payroll checks in the ACH Generation window. The payroll person had mistakenly deleted the build and the direct deposit ACH file was gone. (See tech doc 865591 that says this can't be brought back).

They have not been doing a backup before processing payroll which is recommended. I should be more clear. Doing a backup before processing payroll is extremely important. Most of the time processing payroll is completed without errors. On the occasion where there is an issue, the payroll and employee files are completely messed up. Event such as power outages, server restarting with/without explanation, computer crashing while posting, etc. will wreak havoc to the payroll sub module. Often there is no recourse besides restoring to a backup.

There was no backup before payroll processing. A full days work had been done by many departments. Looked like there was no solution.....we did come up with an acceptable work around however. After discussing we decided that they would:

1. Restore last nights backup to a test company. (The payroll batches had been entered the previous day.)
2. Re-run the payroll in the test company.
3. Verify numbers from summary reports in test company versus live company
4. Create the ACH file from the test company. They had an old ACH file and could look at the header and change information where appropriate.
5. Recommended working with their payroll processor (Wells Fargo) to make sure the ACH file was correct.

Luckily there was a fairly simple work around instead of voiding out the payroll checks and reentering the information again. Once again, do a backup before processing payroll.