Knowledgebase
[QODBC-Desktop] Waiting all day for a query
Posted by brad waddell on 12 March 2009 05:21 PM

Troubleshooting: Waiting all day for a query

Problem Description

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

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

Me wait long time

Solutions

Check Updates of QODBC or QB

     First, you need to 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 is 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:

sp_optimizefullsync customer

in VB Demo.

     See: How to setup 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 desktops 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:

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

(121 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).