subject: Dynamics Gp Fixing Incorrect Records Directly In Sql [print this page] Lets review typical cause of incorrect records and the way to fix them. If you are not Great Plains consultant then this publication is information only and we do not recommend do it on your own risk. Here we assume that you are on Microsoft SQL Server. If you are on Pervasive SQL 2000/Btrieve you can also do repair via ODBC compliant interface for example Microsoft Access assuming that you have produced DDF files. Lets begin with why did it happen?:
1.Typical cause is computer crush while batch posting is in progress. There might be pluses and minuses about GP architecture. If you are posting from client workstation (not process server) then your computer has partial control and should be running until posting complete. It might be categorized as two tiers (not three tiers where there is application server). Lets try to review the following example. You are posting SOP Invoices. When invoice is in work status for the items quantity is shown as quantity allocated. When invoice is posted the next step is to mark quantity as Sold. If the next step doesnt happen then quantity allocated will be permanently incorrect
2.Now lets talk about fix. Of course backup is good idea before you update table in SQL query. I usually do table backup: select * into IV00102_Backup from IV00102 would be a good example of table backup. If you are able to make full company database backup then give a try to check links: Dynamics GP -> Maintenance -> Check Links and select inventory tables. In the example described above it will not fix the problem. But situations are often unique so in your case it might work and issue is at this point will be forgotten. Lets think that SQL update is required. In Item quantity table there are two fields to update: quantity allocated and sold. Obviously the first one should be decreased and the second increased on the same amount
3.What to do if you updated incorrect records? I usually drop DEX_ROW_ID column in backup table then delete original one and finally: insert into IV00102 select * from IV00102_Backup. At this point table is rolled back. If you think that you are ready for the second attempt then go ahead
Lets answer few questions:
Q. Technique looks good but we have no idea about table name. Where is table diagram?
A. Probably the simplest way is Tools -> Resource Description -> Tables and there apply your intuition. For example SOP10100 is Sales document header. If you can find it then you should be able to locate your table in question.
For additional information please call us: 1-866-528-0577, 1-630-961-5918 or email us: help@albaspectrum.com
