Archives

How to Wreck Your M2M Database in Three Easy Steps

My company shuts down over the holidays, but I received a frantic email from one of the companies we support. The user told me that M2M had deleted fiscal year 2009 and he was unable to fix it. To be honest, I thought the user was mistaken because I knew that you could not delete an accounting year with postings in it. In fact, every single month besides December were closed as well. A quick call to one of M2M’s best tech support reps confirmed that there is no way to do this. However, upon investigation, I determined that the user was correct. Somehow, M2M had deleted fiscal year 2009, but I had no idea how.

M2M deletes fiscal years? What are the three easy steps?!?!

Well, after actually speaking with the user, and reconstructing things, I figured out what happened. Unless you have a test server to work on, you’ll just have to trust me that this actually works as you do not want to do this to your production database. If a user opens the Accounting Years Screen (AYRS) and navigates to the current year, they cannot delete it and will receive the error below.

AYRS Delete

This is what should happen.

However, they can delete a fiscal year in the following three steps:

  1. Click Modify on the Accounting Years screen.
  2. Change the Thru Date and From Date to next year’s date.
  3. Click Save and watch the magic happen.

M2M actually issues deletion statements to the GLRULE table for every month of the current fiscal year. I documented all of this using SQL Profiler. I will post a video in the future on the use of SQL Profiler for M2M administrators. When you look through the profile trace you will see the following statement passed 12 times (one for each month):

DELETE FROM glrule WHERE IDENTITY_COLUMN=@P1

So, how did you fix it?

Well, I was very fortunate in the timing of this event. As I said, we shut down during the holidays. This user was working during the shutdown. I was able to restore the entire database from back up and fix the problem without losing any data at all.
The one saving grace is an error triggers the first time a user attempts to post a transaction to the now deleted fiscal year. Therefore, the problem should be quickly discovered. Also, the only table changed is the GLRULE. Microsoft SQL Server does not support the restore of a single table (third party products like Quest Lightspeed can). However, you could restore the back up from the previous day to as a different named database and then append the records to the damaged table in your production database.

What was M2M’s response?

Well, when I showed the M2M Tech exactly what happened, he was as shocked as I was. He tested version 6.0 and it still has the flaw. Therefore he issued a change request and is pushing for the fix to be included in the 6.01 release of M2M which is supposed to be released this spring.

There is one other thing I learned about while troubleshooting this issue and I will share it with you in the article below.

Related posts:

2 comments to How to Wreck Your M2M Database in Three Easy Steps

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>