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.
The Symptom
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
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 Cause
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.
The Patch
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:
cd c:\qbfix
rundll32 QBAliasFix.dll,QBAliasFixAttach
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 🙂
will your qbfix also fix my problem?
Thank you for looking at this for me. I’ve been aware for the problem since March
olalias.cpp (132) : MESSAGE: Fri Jan 23 15:05:55 LVL_ERROR–Error: Verify Online Banking Aliases: Alias hdp*nat’lalertregist866-584-444 points to names list recnum 1625, but has invalid timestamp 1185093396.
olalias.cpp (168) : MESSAGE: Fri Jan 23 15:05:55 LVL_ERROR–Error: Verify Online Banking Aliases: Alias hdp*nat’lalertregist866-584-444, pointing to names list recnum 1626, is a duplicate of another alias.
prefuser.c (17001) : CHECKPOINT: Fri Jan 23 15:05:55 Permissions Analysis Starting…
prefuser.c (17033) : CHECKPOINT: Fri Jan 23 15:05:56 Permissions Analysis Finished.
verify.c (643) : CHECKPOINT: Fri Jan 23 15:05:56
Jacob – it’s possible, but I’m not sure it will without modification. Both of those issues appear to have an offending apostrophe – so assuming QB attempts to clean the records the same way – the delete call would fail.
My code is pretty picky about looking specifically for the SQL for invalid aliases, so it will probably ignore the bad SQL in this specific case.
What I would suggest is:
1. backup QB
2. run the patch in TEST only mode as per above
3. send me the log that the patch produces.
4. restore from your backup (just in-case)
i suspect the log will have some lines like this:
dbpp_execute_imm:: {SQL STUFF}
once i understand the SQL it passes, I can add that the the list of things I look for and see it simply removing the offending clause is safe enough, otherwise I would have to write some new code to rebuild the SQL statement with the correct escaping semantics.
Thanks for a terrific Util … worked terrifically … saved me huge headaches. If you were Ebay you’d be AAAAAAAAAAAAAAAAAAAAAAAA+++++++++++ vendor…
Thanks for the fix. I had this problem previously in QB 2006 or 2007 and had to have intuit correct the issue. I am a long time user of QB (since it first came out) and see that Intuit still uses “us” as the beta testers and can’t apply a fix for this issue. They don’t want to because then they would not be able to make more money. You are a lifesaver!!!
Thank you very much, Monday I found the problem and try to solve it with Intuit, I felt blackmail after talking with their technical support at Manila, when I got their Headquarters 1 or 2 miles away from me. It really work the diference betwen your example and my issue was I got fedex kinko’s. Thank you again
Salvatore – no problem. Glad to see my effort working for more than just me 🙂
Second time using it and it works like a champ. Thanks for the help!
Hi!
I hoped this would work, but it doesn’t seem to be. It says it attaches, but then doesn’t add anything after I run the repair and verify.
This may be because I have 2006 and 2009 installed and I have the issue with 2006, but perhaps your DLL is attaching to 2009.
Is there some way to get in an manually delete these names? I see it has issues with four aliases – all of them are some form of Barro’s Pizza, so I do suspect that apostrophe to be the issue.
The saddest part is I’m doing this to have a good file before I upgrade to 2009 since Inuit discontinued online banking support for 2006 as of today.
I had rolled back from 09 after find the online banking to be terrible compared to 06 and now it looks like I have no choice. 🙁
I think I will go ahead and upgrade and see if the 09 can fix the issue or perhaps your program will work once I have the file in 09, although I only had 06 open and your program said it attached.
Perhaps consider putting a version ID on your attach line to confirm which version for those of us who run mulitiple versions.
Thanks for your efforts in assisting others!
Cindy Fox
thanks!!!!!! i have been working on this problem on and off for two years. i heart you. a lot.
THANK YOU BRETT!!! Worked for me after Don Pablo’s brought Intuit to its knees. I reloaded you page (without my ad-blockers) so I could click all your ads but couldn’t even find any. Man, you should put up a paypal donate button or something. You are doing folks one heck of a nice service here. Thanks a ton for sharing your talents. Best of luck to you.
David,
Glad it worked for you. You’re a funny man on the ads… I guess they always seemed a little cheesy to me, and I don’t get enough traffic to merit trying to cover my bandwidth costs. So enjoy the karma and pay it forward 🙂
Cheers,
-Brett
Thank you so much Brett. My file has been corrupt for over a year. I ran your fix in QB 2006 a couple of weeks ago and it did not work. You told me you ran it successfully in 2007.
I upgraded to QB 2008. I ran your fix and weeeeee!!!!!!!!
I can verify my data
Thanks so so much
Jacob S
P.S.
is it safe to continue doing online banking?????
jacob – awesome. i guess we now know that my code does NOT work for QB 2006 users. 🙂
I have an invalid alias pointer without an apostrophe. My backups never complete without first instructing me to rebuild my data, and rebuilding the data never solves the problem. I see that your fix is only for pointers with apostrophes. Any suggestions for how to simply remove invalid pointers?
…Backing up, then restoring from the backup doesn’t work. Neither does creating a portable backup and restoring from that.
Hey Steven,
Doing a “rebuild” should remove normal invalid pointers. No idea what is going on in your case. I’d try the QB forums and see if anyone has encountered similar issues.
Thx,
-Brett
Well, don’t I feel foolish. It turns out that my invalid alias pointer DID contain an apostrophe. I followed your instructions, and everything went swimmingly. Thanks!
Steven – glad it worked out!
I Love you man! You fixed my problem! If I knew who you were I would send you a paypal donation just to thank you. Thanks a million!
hah… i’m happy to tell you who i am (see about page) – but no donation necessary. glad it worked for you.
Brett, thanks for volunteering to be the go-to guy on this problem.
I followed your directions to run the test but your log didn’t find any errors:
[3040] Running QBAliasFixAttach
[2456] Attaching To QuickBooks
[2456] DatabaseManager import found
[2456] Attached
[3040] QBAliasFixAttach Injection Complete
[3040] DLL_PROCESS_DETACH
[2456] DLL_PROCESS_DETACH
So I didn’t run the patch the second time, but went back to QB (2009 on XP 2002) and ran the Verify Data utility. It tells me, like it does each time I try to back up, that there is a problem and I should run rebuild. I do that and continue on using the file. But I definitely should fix whatever is wrong. Any ideas?
But thanks for the patch. The reason I went looking and found you is a bookkeeping client of mine has a problem with phantom vendor(s), so I’ll take the patch to their store and try it there.
Thanks very much!
Charlotte
434-962-4799
Thank you, the fix worked great! It was easy to follow and it fixed the problem. Thank you for sharing this awesome fix! You are awesome too!
QB 2008. This worked great, once I followed your instructions. Thank you so much.
Hay Brett:
I use QB 2008 Worked Great! Your instructions were easy to follow. You might update the instructions to tell how to get a DOS prompt. Some users may not know. You might also include a readme with the instructions in your download zip file in case someone needs to run your program again in the future and can’t find on the web. Thanks so much. You saved me $800. Now I can upgrade to QB Enterprise with confidence.
Thanks for keeping this still up. it fixed my problem like a charm. you should have a donate button from paypal at the bottom of the post 🙂 for ref the transaction was entered in jan 2011, and qb08 is fully updated, i guess intuit’s solve was buy QB2009.