Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL Updates on GP Data...
Microsoft Dynamics GP (Archived)

SQL Updates on GP Data? Frequency of Checklinks?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

We installed an auditing program a couple of weeks ago.  One of the features of it is to track changes made to GP from outside of GP. ie SQL updates and such.  What I am seeing is that one of our consultants is updating and deleting lines in the SOP10200 tables using SQL Management Studio.  There could be more on other tables, but I haven't set up all of the audits yet. 

My question is, is this safe?  Why isn't he making these changes in GP?  You might be asking why am I not asking him?  It's because I want a second opinion.  The things he has changed so far are allocations and deleting complete line items.  Coming from SQL, I do not like making SQL updates to a transaction table outside of the application iteself, so these actions make me nervous.

Also, is it normal to be running Check Links and Reconcile on a weekly or even nightly basis?

 

Thanks

Adam

*This post is locked for comments

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at
    Re: SQL Updates on GP Data? Frequency of Checklinks?

    I would agree with most other replies here... If this consultant is a 'real' GP consultant, than he can show his GP certifications and as Richard said, would not act like this. This seems to me just as an self-proclamed GP consultant that shows quite a good knowledge of the ERP, but doesn't really care about the data integrity, and most important, the single original source of reliable data which GP is supposed to be.

    I've worked for several years in a company where we had dozens of satelite apps (home build of course) that were querying data out of GP, but never would have one of those apps changed or deleted directly data in the tables. If we had to so, we used 3rd party tools like Scribe's connectors or eConnect, which carry commands and tools to follow the strict rules of Dynamics GP. If you want to add / edit you own data in GP, buy the Extender module and add your own data fields. Then you can do what you want without disrupting the data integrity in GP.

    Check-links and reconcile should be ran only once in a while when you encounter data discrepancies to fix them. Reconciles are more likely to be run on a monthly or quarterly basis, but Check-links are need only on demand or once a year. I'ld show him the door and go hire some descent MBS partner (certified of course).

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    Re: SQL Updates on GP Data? Frequency of Checklinks?

    All you need to do is click the Multi-Line view button and change the quantities to zero. You can backorder order item and cancel items, commit items on a sales order to a purchase order and a host of other things. This guy needs to go to GP Sales Order Processing training. He is just circumventing the entire module and you are paying the price. How much is your time worth versus his consulting fees. Sounds like he has quote the job security up there. He keeps making problems and then 'fixes'  them.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: SQL Updates on GP Data? Frequency of Checklinks?

    We aren't using an outside app that requires this.  He just does it because it is easier for him.  For example, CSR put in an order, they needed to remove the allocation.  The CSR was on his way back to his seat to fix it and the consultant said, 'I got it', and did a SQL update.  The reason is because we do not know how to unallocate an order in GP except to delete the line.  The consultant thought it was easier to do a SQL update.  I'm not sure why he deleted an entire line through SQL and not GP.  

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    Re: SQL Updates on GP Data? Frequency of Checklinks?

    You are in a tough spot. You have a GP consultant who apparently does not know GP and the implications of doing work on the back-end. There are so many inter table relationships that are now being destroyed you will get to a point where GP will not be able to give you any accurate results. A real GP consultant who cared about his/her client's data would never do this. Do you have some outside program that is requiring these updates? It almost sounds like you have some home grown application and GP is being forced to match the data in this outside program. Personally I would show this consultant the door amd make sure the door hits him on the way out.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: SQL Updates on GP Data? Frequency of Checklinks?

    This consultant's answer to most of our problems are with SQL updates then run Check Links to fix any discrepancies that are created by the SQL update.  The problem is that I am a DBA and he is the GP Consultant, so he carries quite a bit of weight when talking to the bosses about GP.  The first week we went live he removed all allocations in all of the orders using SQL.  For two weeks, we didnt' know what we had available in inventory.  We had $500,000 invoiced in the wrong month because invoice dates weren't covered enough in training.  His answer?  Let's just change the doc dates with a SQL update.  I fought against that one, but he almost won the argument.  Now he just doesn't speak of his SQL updates.  He just does them.  Now that I have Fastpath AT and can track changes made from outside of GP, I see his work.  He said Check Links will fix it.  So he runs Check Links almost on a daily basis.  

    To me, it just doesn't seem like the normal way to do things, but again, I'm a SQL DBA, not a GP consultant.

  • Suggested answer
    Ron Draganowski Profile Picture
    Ron Draganowski 1,575 on at
    Re: SQL Updates on GP Data? Frequency of Checklinks?

    Hello, Adam.  

    Deleting a record from the SOP10200 manually using SQL Management Studio will probably not cause an eminent failure of GP; however, it is causing data "corruption" if he is not also updating the other tables that have the SOP10200 table as their basis.

    For example, the SOP10100.SUBTOTAL field is the sum of the SOP10200.XTNDPRCE column.  Is he updating that?  And besides that one example, there are probably 20 or 30 fields in the SOP10100 table that should be updated, as well as 10 to 15 other tables that should also be updated, most notably are inventory allocations in the IV00102 table.

    So, short answer, you shouldn't manually delete a GP SOP line from  the SOP10200 table using a "DELETE" statement in T-SQL.

    If this has been going on, you probably have a bit of cleanup to do.  You should be able to find an article in CustomerSource recommending regular maintenance for GP.  It lists should should be run, and in what order.  The frequency is up to you, based on the amount of mess you find.  Quarterly may be a good idea.

    Good luck,

    Ron Draganowski
    Manager, Technology Services
    RSM McGladrey, Inc.

    Minneapolis/Saint Paul, Minnesota
    ron.draganowski@mcgladrey.com

    http://www.mcgladrey.com

     

    Find me on LinkedIn: http://www.linkedin.com/in/rondraganowski

     

     

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,730 on at
    Re: SQL Updates on GP Data? Frequency of Checklinks?

    If I were you I would put an immediate halt to all of this activity. Reconcile is simply trying to correct all the damage done on the back-end with the SQL scripts. Now your purchase receipts table and your inventory historical trial balance table and a whole host of other tables will no be corrupt. What the sense of having an integrated accounting program if you are simply going to override everyhting it is trying to do. Sounds like a progrgammer who has no idea of with what he/she is dealing.

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,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans