QuickBooks has a nasty bug, that if it bites you – can be quite painful. As of this writing (Dec 2008), Intuit has not issued a patch for the problem yet.
Users first notice the message if they try and 'rebuild' or repair their data file. The rebuild appears to succeed, but later on a "Verify" fails. If users have QuickBooks set to auto-backup and have the "Verify before Backup", then their backups never complete as the verify always fails.
Note, I'm sure there are many causes of this same "will not verify" symptom and this "fix" only addresses one of them.
If you look in the verify logs generated by QuickBooks, the telling sign is an entry like this:
olalias.cpp (145) : MESSAGE: Tue Jul 15 19:20:54 LVL_ERROR–Error: Verify Online Banking Aliases: Alias Lowe's Hardware Store points to invalid names list recnum 197.
This thread on the QuickBooks forums goes into more detail.
The problem stems from a bug in Online Banking feature, when users 'alias' certain payees to a single entry. If the name of the payee contains an apostrophe character (as in Lowe's Hardware above) then the problem manifests.
The problem is due to some buggy code when the 'rebuild' process attempts to clean up no longer used alias pointers. It calls a DELETE function in SQL but fails to escape the apostrophe (as you need to do in a SQL statement). Much credit goes to gvwalsh for tracking this down in the above mentioned thread. Due to a badly escaped call, the DELETE command silently fails during the re-build step and then the verify fails as there are bad records left around that shouldn't be there.
To apply the patch, simply follow these steps:
1. BACKUP YOUR QUICKBOOKS FILE. This is really really important. I'm confident that it will work for you, but remember you are a guinea pig and proceed at your own risk. If the patch damages your QuickBooks file in unintended some way, you will want to be able to restore a backup.
2. Download the QBAliasFix.zip patch and unzip to c:\qbfix\.
3. Start QuickBook
4. From a DOS command prompt, run the following commands:
5. The first prompt you will see, is it asking if you want to run in TEST mode:
TEST mode, will simply log the offending deletes to the log file without making any changes. You should answer YES for your first run though.
6. Next the patch will attempt to attach to the running copy of QuickBooks. You should see a dialog like this come up:
This indicates the patch has attached OK. You are now ready to get rebuild.
7. Goto File | Utilities | Rebuild Data in the QB menu to start a rebuild.
8. After the rebuild has completed, exit QuickBooks. The patch should have created a QBAliasFixLog.txt file on your desktop – it will automatically open this in notepad for your convenience.
9. Carefully check through the LOG file, you are looking for a pair of lines like this:
[xxxx] dbpp_execute_imm:: DELETE FROM I_OLB_ALIASES where key_fld='lowe's Hardware Store' and rec_num='1851'
[xxxx] BAD QUERY WOULD BE REPLACED WITH: DELETE FROM I_OLB_ALIASES where rec_num='1851'
The patch has detected that the DELETE statement has an unmatched apostrophe in it (the one inside of lowe's) and it suggesting the removal of the key_fld='lowes's Hardware Store' clause from the statement. Removing this is OK – as there is a specific rec_num id passed in the where clause. Make sure the suggested replacement SQL command looks OK to you. If it doesn't – drop me an email at brettm-at-gmail-dot-com and I'll look into it. If it does you are all set to run for real.
10. Repeat steps 3-8, except this time at step 5 answer NO to run in normal mode and it will perform the replacement DELETES.
11. After the rebuild is over, exit quick books and save your changes to quit the patch.
12. Finally, run QuickBooks again and select File | Utilities | Verify Data to validate that it worked ok. If things are good, you should succeed and see no errors in your log.
Hey presto – hopefully I just saved someone else $800 to pay Intuit to rebuild their file, and perhaps may help inspire Intuit to fix the bug for real.
The Source Code
I respect that downloading some random program off the Internet and running it against a very confidential QuickBooks data file might be a scary thing. For this reason, I'm publishing the source code of this patch as well as the pre-complied binaries. I welcome anyone to take a look and comment on the source, and/or build their own version of the binaries from it.
I also thought, some developers might find the source code a useful sample on how to develop a DLL in C++ that accomplishes DLL injection and hooks system API calls.
How Does The Code Work?
- The program is a DLL that is Injected into the QBW32.EXE process at runtime.
- It uses RunDll32.exe to launch to save having to write an .exe to boot-strap it, if you look in the QBAliasFixAttach function you will see that it searches for a running instance of QBW32.EXE and then calls InjectDllIntoProcess which is a function derrived from Matt Pietrek's "Windows 95 System Programming Secrets" code. (I actually worked on the Windows 95 project as a software developer at Microsoft – OK… now I'm showing my age…)
- InjectDllIntoProcess uses the well known CreateRemoteThread injection technique to force the remove QBW32.EXE process to call LoadLibrary (disguised as a ThreadProc parameter) on our QBAliasFix.dll library.
- Once QuickBooks calls LoadLibrary on our library, then the code hits the DllMain section, when check that the host process is indeed QBW32.EXE, and if so proceed with the hooking calling AttachToQB.
- We then enumerate through all of the libraries that are loaded into the QBW32.EXE process, looking for DatabaseManager.dll, which is where the SQL DELETE code happens. Fortunately this code statically links to dblib9.dll to call the dbpp_execute_imm function. We then call the HookImportedFunction method on this module handle, which does the hard work to rummage through the imports memory block and patch the import table to map to our hooked_dbpp_execute_imm function instead.
- Once things are patched, we're rocking and rolling. If the offending code gets hit then hooked_dbpp_execute_imm looks at the SQL passed to try and detect bad apostrophes. The 'detection logic' is a bit lame – I simply alert if there are any apostrophes – escaped or not, as I didn't want to bother writing a SQL statement parser. If an alert triggers, I hack off the part of the where clause that is not needed and pass it on down after confirming with the user.
I have only tested this with WinXP and QuickBooks 2007 and only for my corrupt file that has a single bad alias with an apostrophe. I would love some others to run it that might have a corrupt file and see if it fixes it.
What has all this to do with my Ultralight Backpacking Blog. Absolutely nothing – just a convenient place for me to post the information. 🙂
Update Dec 24th 2008
One person that has tried this patch has run into issues where it only prompts for the first 2 or 3 alias fix-ups in a file that have many (32+) bad aliases. I'm working on a fix for this, and suspect it has to do with timing/re-entrancy issues due to the popup-window. Once I've done a little more testing I'll post an update – the next version will have 2 modes – a "test mode" that spits out logs about what it *would* do and a normal mode that silently repairs the SQL without prompting (step 7 above). It appears to run OK if you keep rebuilding – as it catches 2 or 3 each rebuild; stay tuned for a fix if you QB file has many bad aliases and let me know you experience either way.
Update Jan 8th 2009
I rebuily version 1.2 of the patch to remove the prompting option and have just a TEST mode and non-TEST mode, this should work better for files with large numbers of bad aliases. Enjoy!
Update August 2009
Several folks have now reported success with QB 2007 and QB 2008 on Vista and XP. At least one person has tried with QB 2006 and the patch has NOT worked – it causes QB to crash. I don't intend to make a fix for 2006 at this point, mostly because I don't own a copy 🙂