Microsoft Dynamics GP (Archived)

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

(0) Share

Report

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

I have the same question (0)

All responses (18)

Answers (0)

Ian Richardson 4,150 on at

Like (0)

Report

is it possible that you have reached the max number of employees you are licensed for? Granted that should not over write records you should have got something along the lines of what this tk talks about

support.microsoft.com/.../853103

Was this reply helpful? Yes No
SQLAdmin 285 on at

Like (0)

Report

No, it was just a one-time occurrence and GP is working fine now so MAX/LIMIT is NOT the issue.

What we are trying to see is whether anyone else has ever come across a scenario when GP overwrites an existing record when user is entering a new record.

Essentially, prior to HR Manager entering the new employee, UPR00100 contained a row like this:

EMPLOYID 100

LASTNAME Smith

FRSTNAME John

Then, after they submitted new employee (Jane Smith), GP issued an UPDATE command to change:

EMPLOYID 100 ============> 102

LASTNAME Smith ============> Doe

FRSTNAME John =============> Jane

So instead of INSERT-ing a new row 102, an existing row 100, ncluding the primary key EMPLOYID, was changed.

Has anyone else come across this or can explain it?

All help explaining this will be much appreciated!

Was this reply helpful? Yes No
SQLAdmin 285 on at

Like (0)

Report

Researching this issue some more, we are thinking perhaps it's related to Dynamics table buffers (msdn.microsoft.com/.../cc543579.aspx )

Is it possible for Dynamics GP to have a bug/glitch that would result in table buffers not cleared before new data is entered?

So in this case, perhaps the table buffer for UPR00100 did not clear data for EMPLOYID 100. So when HR Manager entered a new employee, instead of sending INSERT command to the database, GP sent UPDATE command to update EMPLOYD from 100 to 102.

Thanks!

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report

It is possible albeit very unlikely. If you suspect something like this is taking place you should consider opening a support ticket with Microsoft detailing all steps to recreate the issue. Even if we knew how it happened, it would have to be something Microsoft addresses directly.

Was this reply helpful? Yes No
SQLAdmin 285 on at

Like (0)

Report

Thanks!

This particular GP install is pre-2010 so Microsoft no longer supports it.

We stepped through each SQL command issued by GP one by one (using LDF Log file tool) so we know that an UPDATE T-SQL command was issued which modified EMPLOYID field in UPR00100 table.

Perhaps no one has seen a of a primary key being updated (EMPLOYID in this case) because most people are using much newer versions of GP?

Was this reply helpful? Yes No
Galina 1,077 on at

Like (0)

Report

There is no problem updating primary key on any of GP tables even in the newest version since there is no referntial integrity implemented.

Was this reply helpful? Yes No
SQLAdmin 285 on at

Like (0)

Report

The issue is that the primary key of UPR00100 was updated INSTEAD of inserting a new key when user entered a new employee using Employee Maintenance screen. Primary key (EMPLOYID) is greyed out so the only way it could have been updated is due to a bug in GP (e.g. previous user was cached and then modified).

We are just wondering if anyone has encountered this issue, perhaps it's only in older versions of GP (pre-2010).

Thanks!

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

I would take Mariano's advice and enter a support request with Microsoft.

Was this reply helpful? Yes No
SQLAdmin 285 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Was this reply helpful? Yes No