subject: Dynamics Gp Data Repair Sql Queries And Physical File Recovery [print this page] Formerly this ERP system was also known as Great Plains Dynamics. If you are reading this paper chances are high that you got data corruption or integrity issues. Lets review possible scenarios and try to give you recommendations. Please if you would like to tray repair on your own we have to mention that information provided as is without warranty. Each problem is unique and requires initial analysis. So this paper is intended to be initial step in your homework:
1.Maybe you are OK. Have you tried Check Links? File -> Maintenance -> Check Links is the path. What it does it checks related records in table cluster and if problem is simple it may add missing counterpart or remove orphan row. Be sure that you have regular backup as there are known cases where poorly coded Dexterity customization causes CL remove custom table records. Have you tried it? Did it fix your situation? If not then lets move on to the next advice
2.Is it by any chance something associated with moving Great Plains on Pervasive SQL to new server (versions 7.5, 7.0, 6.0, 5.5 and earlier). Location translation is not a corruption it just requires several steps to translate old path to the new one. Please see our earlier publication. However it might be too technical so also consider to engage consultant
3.Batch posting interruption. There is Batch Recovery screen which should be opened automatically on your next login to the system. Often it helps but sometimes doesnt. If it doesnt fix please first read report as it might be Edit Required. If you see that then simply open the batch and print Edit List report and look if any error is reported. The most popular problem is missing GL account in document distribution. If so just open this document drill down to Distribution and add account
4.Batch Recovery doesnt help. Lets review what is going on when you are posting batches. Documents are moved from work tables to open (or directly historical as it is in Sales Order Processing module). Then work records are removed, master (such as customer or vendor) tables are updated (summary by month, quarter or year). There is possibility to use so-called Process Server to outsource posting to another computer (you may think about it as Application Server layer as it is often referred in three-tiered ERP applications). If you do it that way then whatever happens with your computer (power outage, coffee cup spill disaster, etc.) batch should be posted all the way to General Ledger. But especially in small business environment users initiate the process from their workstation and in this case process is driven by your client application. If computer shutdown aborts the process then you may imagine the mess in work, open and summary tables. SQL surgery might be required. Usually it is in Query Analyzer and update statement on SY00500 and SY00800 in each case plus specifically in modules for example in the case of SOP these would be SOP10100, SOP10200 and SOP30200 and SOP30300
5.File corruption and backup is not available. We have seen situations when backup was picking up backup files produced by MS SQL Server (you saved on direct SQL connection option in Seagate or Arcserve) but Server Agent stopped and there is no backup for the last several months. This unpleasant discovery typically happens in small companies without strong IT department who would monitor Server Agent. Now your hard drive crashed and what do we do now? There is a possibility that files from crashed HD could be recovered. Make your research on Google about Data Recovery Software if you want to try it out on your own or call local data recovery consultants. Files to recover are in SQL server directory with GPS prefixes such as GPSDynamics.mdb for example. When you got them back in SQL Query analyzer use sp_attach_db
Lets now answer several questions on the topic:
Q. We are not sure if we got file corruption or mess with rows in the tables. What is the best way to get additional details?
A. Typical recommendation is to appeal to consultant. But if you think that you can do the job then open Dex.ini file and enable logging. The following lines: SQLLogSQLStmt=FALSE, SQLLogODBCMessages=FALSE and SQLLogAllODBCMessages=FALSE should be flagged to YES. Then login client interface and at the moment when you get error message stop using Great Plains and open log file DEXSQL.LOG located in client application directory as well as Dex.ini. Review lines at the bottom of the log file and make the judgment about your next step. Be ready to have fun in resolving the puzzle
Q. We are small company and we are at the point where we cannot use Great Plains because we are getting error message while trying to login. We cannot pay for more than couple of consulting hours. What would be your recommendation?
A. You should do your research first on Customer Source or if you do not have access there then on Google. A lot of technical issues are documented and published on the internet. Then when you understand the nature of the problem call consulting firm for precise fixing procedure
Q. We are on Great Plains 7.5 on Pervasive SQL 2000 and our server HD crashed. We have to appeal to data recovery services but all we need is our GP database related files. Where to look?
A. It is likely Dynamics folder on the server with subfolders named as your company ID. You should see BTR and IDX files there
Q. Could you elaborate a little bit about Location Translation related error messages?
A. No problem. Go to Tools -> Setup -> System Setup and open Location Translation. Now imagine that your old server Dynamics folder was in H:Dynamics and the new one is over UNC patch \NewServerDynamics. Pervasive SQL Server/Btrieve they talk to the database via operating system drivers and need to know where your data is located. And this is exactly what you need to translate from old to new in this screen
For additional information please call us: 1-866-528-0577, 1-630-961-5918 or email us: help@albaspectrum.com
