subject: Great Plains Dynamics Data Repair In Pervasive Sql Control Center [print this page] The goal of this small publication is to give you directions when something happened on database recording level and you need to change field value or remove row directly in the table and not through client interface. Of course if you are on Microsoft SQL Server database platform then the job could be done easily in Query Analyzer. However if you are on Pervasive SQL/Btrieve platform then you need to do several technical steps prior to getting control over the tables. Lets begin:
1.DDF or Data Definition Files. Btrieve technology needs data definition (file, index and field.ddf files). Old versions of Great Plains including 7.5, 7.0, 6.0, 5.5, 5.0 had GenDDF utility which allows you to produce DDF. To give simple idea on what it does lets say that it looks into Dexterity meta dictionaries (Dynamics.dic or any third party ones defined in Dynamics.set) and creates data definition for tables, fields and indexes
2.ODBC connection. Simplest way to create ODBC DSN is through Pervasive SQL 2000 Control Center (we are now talking about old versions of Great Plains and not something that is going on in 2012). You need to copy DDF files to company directory on the server and create bound connection in Pervasive Database creation wizard
3.Data repair. Consulting Great Plains since mid 1990th we used Microsoft Access with Linked Table mechanism. However with introduction of Pervasive SQL the same job could be done in Control Center. There are advantages here as you can use aggregated SQL queries to address millions of rows in the matter of minutes. Lets take a look at syntax
SELECT JRNENTRY,HSTYEAR, sum(CRDTAMNT) as CREDIT,
sum(DEBITAMT) as DEBIT,
sum(CRDTAMNT) - sum(DEBITAMT) as Difference
FROM "GL30000"
where HSTYEAR=2012
group by JRNENTRY, HSTYEAR
having sum(CRDTAMNT)sum(DEBITAMT)
This script is looking for GL entries in the year of 2012 which are not balanced. Please note that GL30000 table reference is in double quotes ()
Lets assume that you found imbalanced transactions and ready to remove them (and then post correction record into historical year). Here is the script:
delete from "GL30000"
where JRNENTRY=163598
Lets now answer popular questions:
Q. In our Dynamics directory we do not see GenDDF utility. Are we missing something in the logic?
A. You can install it from installation CD. In majority of the cases we can see it already available as former consultants were designing Crystal Reports or doing data fixes
Q. We are on Great Plains DOS and version reads 9.5. Are we reading relevant post?
A. The answer is both Yes and No. GPA DOS was Btrieve application and you need DDF files in order to connect via ODBC. Large portion of GPA customers had their server redeployed on Pervasive SQL and we think that you should be aware about Control Center and SQL scripts. Of course in your case table and field names are different
For additional information please call us: 1-866-528-0577, 1-630-961-5918 or email us: help@albaspectrum.com
