Troubleshooting: Waiting all day for a query

Problem Description

     I wrote a process to retrieve RefNumber from QuickBooks Invoices and save them to an external reporting system. It was working fine, taking on average 2 seconds per record to complete the interaction with QODBC. Seemingly out of nowhere, it quit working (requested time out), so I started investigating.

     When I connect to QODBC Support Wizard, the application hangs for a long time. It was touched when I got back almost 15-20 minutes later. When I try to run a simple query in QODBC Support Wizard, however, it just goes and goes and goes, apparently without end. See the screenshot below - 54 minutes, but it's still going at 1 hour 10 minutes. No error message, but clearly, something has gone wrong.

Solutions

Check Updates of QODBC or QB

     First, you must check what has been changed, such as whether the QODBC or QB has been updated.

Repair Optimizer File

     By the look of things, the optimized file was corrupted. Try running:

SELECT Top 1 * FROM Customer unoptimized where TimeModified > {ts '2007-06-01 00:00:01'}

And if that works, try repairing the optimized table by running the following:

sp_optimizefullsync customer

in QODBC Support Wizard.

     See: How to set up QODBC Optimizer and where are the Optimizer options for more.

Optimizer Setting When Using Link Server

     A linked server runs queries as multiple threads, and the QODBC optimizer is single-user only. So, you need to set up a different optimizer file for the SQL Server to use than the other desktop applications. To do this, step up a separate System DSN (see: How to create or configure a QODBC DSN) and specify a fixed location of the optimizer database folder for SQL Server to use (like C:\Program Files\QODBC Driver for QuickBooks) instead of the default %UserProfile% method.

     To repair the optimized customer table, you can run the following:

SELECT * FROM OPENQUERY(QBs, 'SELECT * FROM Customer VERIFY WHERE ListID = "x"')