Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / For all GP Experts: Ca...
Microsoft Dynamics GP (Archived)

For all GP Experts: Can you explain how did this data get overwritten?

(0) ShareShare
ReportReport
Posted on by SQLAdmin 285

We ran across a really strange GP issue and wondering if anyone else has seen the same over the years.

We have been using GP for the last 5+ years and this is the first time we came across this.

Our HR Manager was adding a new employee using the Employee Maintenance Screen from Great Plains. Instead of creating a new record in UPR00100 with a new EMPLOYID, another employee’s record was overwritten, including their EMPLOYID.

How do we know that EMPLOYID was overwritten on the database level?

Well, we opened database’s LDF (log) file using Apex SQL Log tool and analyzed every single transaction that occurred precisely around the time that the employee’s record was overwritten.

Here is what we saw: Instead of issuing an INSERT command against UPR00100 to create a new record for the new employee being entered, Great Plains issued an UPDATE command, which modified EMPLOYID of another employee and updated LASTNAME, FRSTNAME and other data to the information submitted from the Employee Maintenance screen. Old EMPLOYID was completely gone from UPR00100.

Can anyone explain how this could have happened? The EMPLOYID is greyed out on the Employee Maintenance Screen so user could not have typed over the EMPLOYID.

We already fixed this issue using Check Links but it really worries us that this could happen in the future.

Has anyone else had this happen / seen this happen in Great Plains? (Existing EMPLOYID overwritten when entering a new employee).

Thanks in advance!!

*This post is locked for comments

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,003 User Group Leader on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    1. This is a very old Dynamic GP version (pre-2010 Great Plains install).

    What version is it actually?

    2. This is inside HR module and note core GP (hence, table UPR00100).

    HR Module dictionary or GP, both should have same table information for UPR00100, BTW do you have any customization on your GP?

    3. This only known to have happened with this install once, and GP was used by this HR department for 5+ years.

    You should be thankful that this happened only once in all those years. :)

    4. Instead of the INSERT, UPDATE was called for the last edited record, suggesting that GP somehow kept copy of last edited record in table buffer/cache (instead of clearing it).

    Also check your table PK on SQL.

  • Galina Profile Picture
    Galina 1,075 on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    So, the system did something that it wasn't programmed to do? Could it be budding artificial inteligence? I wouldn't be upgrading anything, on the contrary, left everything as is and see what happen...

    TGIF, everybody!

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    No, we have not seen anything similar. So, if you are concerned about this happening again then it's time to upgrade.

  • SQLAdmin Profile Picture
    SQLAdmin 285 on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    We carefuly reviewed line by line each command in the database transaction log (LDF file) using Apex SQL log tool.

    There are no INSERT statements and no commands executed outside of the commands called from GP (e.g. no one manually executed a query to change the primary key).

    SQL Server LDF file provides definitive low level sequence of events so there is 100% certainy that GP issued an UPDATE T-SQL command against the database to change EMPLOYID (primary key). There is 0 chance that something else happened.

    However, few things to keep in mind:

    1. This is a very old Dynamic GP version (pre-2010 Great Plains install)

    2. This is inside HR module and note core GP (hence, table UPR00100)

    3. This only known to have happened with this install once, and GP was used by this HR department for 5+ years

    4. Instead of the INSERT, UPDATE was called for the last edited record, suggestign that GP somehow kept copy of last edited record in table buffer/cache (instead of clearing it)

    If anyone has seen anything similar over the years, would definitely be interested to know.

    Thanks again!

  • Tim Foster Profile Picture
    Tim Foster 8,515 on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    In your SQL logs did you see *any* INSERT statement for the "new" record?  In some GP tables the developers INSERT a record populated with only the Primary key data and blank for everything else.  Mariano's comment about checking for triggers got me thinking of Patrick Roth's article about triggers breaking record locking:

    blogs.msdn.com/.../sql-db-insert-trigger-breaks-dexterity-active-locking.aspx

    After the blank record is inserted, the "new" record is treated as an "old" record and only UPDATEd.  I think the Dexterity developers do this to save code.

    Is it possible that the DEXTERITY code "loses" the Primary key (EMPLOYID) and updates the wrong record?  I'm not suggesting a bug - there are many ways the value on the form can be changed - all of them "bad" and done by "customizations" on the window.

    Tim

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,003 User Group Leader on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    Missed Facebook like Button here. :-(

    EXTREMELY INFINITESIMALLY REMOTELY..

    Liked (Y)

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    Ok, then... straight to answer your question.

    No, I have not EVER encountered this issue. If the question is, is there a chance for something like this to happen then answer is EXTREMELY INFINITESIMALLY REMOTELY it will ever happen. Can it happen? I guess if there is a possibility, even infinitesimal, it could.

    Now, I am also a Dex developer and I have to say that the only possible way this could have happened was via an update directly in SQL. So, I would suggest you check the UPR00100 table for any triggers that could potentially alter data.

  • Almas Mahfooz Profile Picture
    Almas Mahfooz 11,003 User Group Leader on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    Mr.Sanjay, I approve your reply (not because you did approve my other reply : ))

    Being a Dexterity developer, I know it can't happen, Dexterity code can not do this.

    Because records get update only if err()=OKAY , and when it gets OKAY ,it only update fields other than primary key.

    Edited Post to add

    P.S. By commenting on this post , I don't mean to be the one from GP Experts :) . I am happy to be just another dexterity developer.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    My sympathies go to Dynamics GP which is facing the user, and it is at the bottom off the food chain.  It is all but natural that Dynamics GP gets blamed when thing go wrong - But to say Dynamics GP updated a primary record instead of insert -  does not sound right (Just a thought from a beleaguered ERP Administrator) .  The complete food chain needs to be investigated.  Though there is statement to the effect in your post that there is evidence "Dynamics GP passed entries to update a primary key",  would encourage you to recheck your evidence.   If you are still sure dynamics GP is the culprit, you can get access to the source code  through your partner  http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/02/09/microsoft-dynamics-gp-source-code-program-for-asia-pacific.aspx, for Dynamics GP and pull out the update statement against the employee id, which I guess you will not find.   

    It more likely a update statement done outside Dynamics GP and I have my confessions.

    Usually I end a post with cheers, but it is real Halloween for you.  Confident you will come out of this strong.

    Sanjay

     

  • SQLAdmin Profile Picture
    SQLAdmin 285 on at
    RE: For all GP Experts: Can you explain how did this data get overwritten?

    This is pre-2010 GP version and Microsoft no longer supports it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans