Great Plains Dynamics Data Repair In Pervasive Sql Control Center

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

by: Andrew Karasev

POST COMMENT

Perform Deleted Image Recovery On Mac Easily Bulk Document Scanning Provides Easy Data Access Hire Ost To Pst Converter Tool For Instant Email Recovery How To Achieve Mp3 Files Recovery On Mac Secured Your Workplace Work By Using Extended Computer Data File Folders Elements Of Ibm Optim Data Privacy Solution Concepts Behind Raid Recovery Why Is It Better To Outsource Data Center Solutions Recovery Of Lost Files The Role Of An Eating Disorder Treatment Center In Recovery Concussion Recovery Often Involves Professional Concussion Management Zappos Hacked: How Data Breaches Affect Us Achieve Photo Recovery From Sd Card