| Waiting all day for a query... |
| Author |
Message |
|
|
| Posted : 2007-11-08 08:51:05 |
I wrote a process that would retrieve RefNumbers 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. I don't know how long--when it locked up I went to play a game of darts with a coworker and it was connected when I got back 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...
Any clue what might be the problem?
 |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-08 09:52:44 |
So what changed? Did you update QuickBooks or QODBC? By the look of things your 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 do I setup the QODBC Optimizer? Where are the Optimizer options? for more. |
|
|
|
|
|
| Posted : 2007-11-09 04:50:51 |
Hi Tom,
I decided to delete the QODBC DSN and start the process from scratch, and I think I made some progress.
Now, I can run queries just fine in VB Demo. The following is the SQL involved in the first step of the process I described in my original post:
SELECT listID from Customer where AccountNumber='ENVI'
In VB Demo this takes less than a second to return the ListID. When I try to run the same query via a SQL Server linked server object, which used to work just as quickly, it still hangs and hangs:
SELECT * FROM OPENQUERY(QBs, 'SELECT listID from Customer where AccountNumber=''ENVI''')
Just for kicks I tried the query from within Coldfusion, and after 189 seconds it returned the proper data. Feeling encouraged, I tried it again using a different AccountNumber and it returned in less than a second.
I would much rather rely on the SQL Server link, however, because we can never go more than a day without receiving a Xerces.dll error using Coldfusion, while the SQL stored procedures were (until this started, of course) fast and reliable.
I have tried deleting and remapping the SQL linked server object to no avail. Where do you think I should I look next?
Thanks... |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-09 08:01:28 |
A linked server runs queries as multiple treads, and the QODBC optimizer is single user only. So you need to setup a different optimizer file for the SQL Server to use than the other disktops applications. To do this step up a seperate System DSN (see: How do I 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"') |
|
|
|
|
|
| Posted : 2007-11-09 08:25:46 |
I made the change you suggested, though I suspect I may have chosen the wrong server on which to create a SQL-specific DSN.
Within our LAN we have a dedicated accounting server (let's call it Accounting) and a dedicated database server (let's call it Database). The QB company file lives on Accounting, while SQL Server predictably lives on Database. Each server is running the iBiz connector, and as I mentioned earlier the VB Demo connection works fine.
On the Database server I created a new DSN called QBSQL and set the Optimizer as you suggested. I then attempted to run the repair SQL command you provided, and it seems to behave just as it was with previous requests -- it's been spinning for 9 minutes now.
Should I have created a SQL-specific DSN on the Accounting server as well?
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-09 08:33:33 |
| You should only be using QODBC on 'Accounting". "Database" would be to slow as far as MS SQL Server is concerned, also the use of the Remote Connector is not supported for use with MS SQL Server. |
|
|
|
|
|
| Posted : 2007-11-09 08:46:37 |
Do you mean to say that linked server objects only work when the company file and sql server are installed on the same machine?
This configuration would work fine with Coldfusion accessing the data via remote connector -- and it does, except that we're dogged by Xerces.dll errors far too often and can't rely on it.
Have we reached a dead end? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-09 08:52:07 |
| Basically, the QODBC Server Edition, the QODBC DCOMs, QuickBooks and MS SQL Server should all be running on the one Windows Server. That's the supported configuration. You're welcomed to do anything else that works for you, but it's unsupported. |
|
|
|
|
|
| Posted : 2007-11-09 08:58:35 |
Okay. How about this...
I can put QODBC back on Database, with an active Quickbooks session running on the Database system console that has the company file open. The open company file would be on Accounting, accessed via a mapped network drive, but as far as QODBC is concerned, it would be dealing with a local SQL Server, local QODBC, and local Quickbooks instance. That is a supported configuration, right? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-10 11:31:13 |
| Yes, running the QODBC Server Edition, the QODBC DCOMs, QuickBooks and MS SQL Server all on your Database server is the supported configuration. |
|
|
|
