When you see the problem similar to what we are discussing in this publication good idea is to keep year open until the fix is applied. In our opinion unbalanced GL entry is unlikely to happen when you are on Microsoft SQL Server until somebody from your consulting team or IT folks mistakenly run delete command against open year transactions table GL20000. But we have seen this issue to happen on Pervasive SQL 2000 DB platform with earlier Great Plains versions 7.5, 6.0 and 5.0. We will be using Pervasive SQL syntax here but of course if you got it on MS SQL Server then it could be done in Query Analyzer. Let's begin with connection to your company database:

In Pervasive Control Center create new database on the step create ODBC DSN be sure that you copied DDF files into company directory on the server. If not then produce them in GenDDF utility

Non balanced entries catching script. You can use something like the following:

SELECT JRNENTRY, sum(CRDTAMNT) as CREDIT, sum(DEBITAMT) as DEBIT,
sum(CRDTAMNT) - sum(DEBITAMT) as Difference
FROM "GL20000"
group by JRNENTRY
having sum(CRDTAMNT)<>sum(DEBITAMT)

Here we assume that problem happened in open year. If you already got it in historical year then script should be modified to something like below

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)

If you got entries in results then you should consider several options to fix the issue.

First Option. You can create temporary dummy account and post correcting transactions with offset to this account. Next step is delete the line in GL20000 table associated with dummy account and making correction transaction not balanced but in being coupled with entries identified above it should do the job. We do not like this approach as in the future these transaction might be confusing next consulting team

Second Option. It is removing identified transaction and posting one correction GL entry with comments (in note). Here is sample script:

delete from "GL20000" where JRNENTRY=1034 or JRNENTRY=1098

Let's think about several advises. First of all do not close open year until the issue is resolved. Why? Because if you close the year then either BBF (beginning balances brought forward for balance sheet accounts) or yearend closing P&L to retaining earnings entry (in historical just closed year) will out of balance and correction procedure will be a way more complex

What happens on Btrieve file level? It is likely corrupt records in GL20000. When you are done with the fix described above try to do the following:
1. Copy GL20000.btr file and rename the old one and again rename the copy to GL20000.btr. Maybe you have corrupt hard disk sector and this procedure will move table to another sectors
2. Run Maintenance - Shrink and then Rebuild. This step should drop broken Btrieve rows

Let us make few final comments. If you are not programmer with good exposure to accounting then we do not recommend you to do data repair on your own
If you are on Microsoft Dynamics GP 2013, 2010 or 10.0 then please call you consulting partner as there is €automatic' fix available on the Partner Source

For additional information, please feel free to call us 1-866-528-0577 or 1-630-961-5918 (this number works for international customers) or email us help@albaspectrum.com We serve you USA/Canada nationwide via remote support (web sessions and phone/Skype conferences). Local service is available in Western Michigan, Chicagoland, Southern California (LA, Orange County and San Diego) and Houston area of the state of Texas