I am running Version 7 Enterprise (Mfg/Wholesale), with the Version 7 ODBC driver.

Intermittently, when I run a set of queries, some tables remain open, as visible in the dialog box.  Last night these tables remained open for more than 10 hours. (last night it was 3 tables fro a Sales query - Sales Credit Memo, Sales - Sales Receipt and Sales -
Invoice).

I can't close QuickBooks while these are still running, and if I close QuickBooks (through task manager), the dialog boxes for these tables are still visible and active (at least the timer is going...) - and I can't see them in Task Manager to shut them down.

Please help.  What is causing this problem, and is there a fix?

Paul

I would need to see a sample set of the SQL Statements used in your queries to hazard a guess why. You should first try switching off "unchecking" the Display Driver Status Panel in QODBC setup:

Tom -

Here are a couple sample scripts:

Hi Tom -

Thanks for prompt reply.

(At the end of this post is a question about a where clause which might be fairly short to answer; for the main issue at hand, here is a more complete explanation of the environment I have.

I have a copy of a Quickbooks Enterprise database that is running standalone on my laptop.  No data is being updated, and because it is on my laptop I know there is no issue regarding multiple users simultaneously running multiple queries.

Because I was running into very long table load times, I went through the database and optimized (using VBDemo32) each of the large tables involved.  I am still continuing to connect to the (now optimized) original database file. (is this the best way to do this, or is there another "optimized" file to use that would still have all current data?).

The queries (at the moment)  are the following:

Customer (about 250 records)

Vendor (about 250)

NonInventoryItems (about 2800)

SalesRep (about 20)

Terms (about 20)

The following tables have 15,000-50,000 records; I am trying at the moment to load 100 from:

Invoice

Bill

Bill Line Item

PurchaseOrder

PurchaseOrderLine

Sales

The syntax for the queries is all as I indicated in my prior e-mail.

The queries are being run sequentially. 

I have tried running with the display turned off as you suggested.

I also looked at the System DSNs and eliminated a Quickbooks ..N DSN that was there.

I have increased the Chunk setting you mentioned to 20,000.  The load is much quicker (thanks for the suggestion).

The queries all run satisfactorily, but after they run I am still unable to close QuickBooks, as it indicates there is an addin or other product running.

I then shut down the application in which the queries are written (QlikView, a data analysis tool).

I still cannot close Quickbooks.  When I look at the task manager,    I see only QuickBooks running as an application.   I do see  two processes running that seem to be related to QuickBooks:

- QBDBMgrN (even though I deleted the Quickbooks..N system DSN and re-booted

- QBDBMgr.exe 

I stop the QBDBMgrN process, and still cannot close Quickbooks.

I stop the QBDBMgr process, and get a message that the connection to the company file has been lost, and Quickbooks needs to close immediately.  There are other comments that refer to making sure that QuickBooks is installed on the same computer, and that one should make sure if QB is installed on the server that the server has not been turned off.   I then have the option to Abort or Learn More, and am connected to the following site:

http://www.quickbooks.com/support/faqs/qb2006/829e206e.html

To this point, I am specifically referring to the "how do I get disconnected" problem.

I have another question:  The QB data file has records going back to 1999/2000.  For my purposes I would like the 100 records I am extracting to come from more recent data, say the first 100 records of 2005.  In come cases I might want to use TXNDate (a string) as the basis for this extract, and in others TimeCreated or TimeModified.   What is the proper syntax that QODBC supports for the appropriate Where clause to accomplish this purpose (e.g., start with records in 2005)?

Sorry for the long post.

Thanks and regards,

Paul

By the sounds of things, some of your queries have timed out and the child process is still in memory. When this happens you will not be able to close QuickBooks and you will need to use the Task Manager to close QuickBooks. I can also have this happen if I crash QODBC by running invalid queries.

As far as the query is concerned, this will extract 100 rows fast:

SELECT Top 100 * FROM Invoice NOSYNC where TxnDate >= {d '2005-01-01'}

but your invoice table must be optimized for it to work!

You can fully sync your Invoice table by running:-

sp_optimizefullsync Invoice

and then update it manually at any point of time later on by doing:-

sp_optimizeupdatesync Invoice

Hi Tom -

I tried to run the sp_optimizefullsynch Invoice statement in the script, and got an error that the statement was not recognized.

I am trying the 100 rows statement now; it has been running for about 10 minutes.

I'm not sure I fully understand the child process issue.  When I run the queries I outlined, I get the 100 rows I'm after, and the script runs without incident.  Unless I have the dialog box open, I would never know there was an issue.

Regards,

Paul

Tom -

I did get the 100 rows statement to run for the TxnDate string, running off the table as optimized with VBDemo.

Please advise the syntax for the timestamp values.

Thanks and regards,

Paul

Hi again, Tom -

Poking around QODBC I looked at the QODBC messages, and got the following:

(FYI I ran the queries at 5:55 AM, and the tables stayed open...)

2006-11-15 05:55:23 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 07:04:04 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 07:13:09 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 07:28:08 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 07:32:02 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 07:46:41 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 07:55:09 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 08:38:30 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 08:42:36 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 08:46:12 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 08:49:50 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 09:22:54 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 09:47:09 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 10:19:05 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 10:20:04 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 10:21:03 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 11:54:35 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 12:02:33 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 12:37:50 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 12:41:00 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 12:42:30 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 12:44:38 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 13:10:52 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 13:21:20 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 13:53:13 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 13:54:04 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 13:56:25 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 13:58:57 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:12:38 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:27:31 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:38:03 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:39:58 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:42:09 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:44:40 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:46:21 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 14:52:11 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 19:39:27 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 20:21:43 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

2006-11-15 20:29:57 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

Do these provide any information pertinent to the issue we're talking about?

Regards,

Paul

The error is saying that your optimized file is corrupt. See: How do I switch OFF or RESET the QODBC optimizer? 

The sample statements are:

SELECT Top 100 * FROM Invoice where TxnDate >= {d '2005-01-01'}
SELECT Top 100 * FROM Invoice NOSYNC where TxnDate >= {d '2005-01-01'}

SELECT Top 100 * FROM Invoice where TimeCreated >= {ts '2005-01-01 00:00:00.001'}
SELECT Top 100 * FROM Invoice NOSYNC where TimeCreated >= {ts '2005-01-01 00:00:00.001'}

SELECT Top 100 * FROM Invoice where TimeModified >= {ts '2005-01-01 00:00:00.001'}
SELECT Top 100 * FROM Invoice NOSYNC where TimeModified >= {ts '2005-01-01 00:00:00.001'}

NOSYNC tells QODBC to read the optimized table directly.

Hi Tom -

We're going to run a clean optimization and test on a stand-alone version of QuickBooks 7 running on a laptop.

To get a fully-optimized file, I understand we need to do the following:

1.  Set up QODBC to auto-login to QuickBooks and start it if it is not running, per the instructions in the QODBC Forum.  Set the Iterator or Chunk Size to >20,000.  Requires a start-up of QuickBooks as Administrator as part of the process.

2.  Go to the command prompt, and type in the following

c:\Program Files\QODBC Driver for Quickbooks\qodbcful QuickBooks Data

3.  This may run for some time on the first optimization (e.g., hours/days)

 Alternatively, would we just use Reload All Data to accomplish #2 above?  Please confirm.

After we have an initially-optimized file, we would like to be able to do updates in 2 modes:

Mode 1:  Overnight

To do this, I understand that, after the initial set-up is completed, we would just run:

c:\Program Files\QODBC Driver for QuickBooks\qodbcupd QuickBooks Data.

(QODBC optimization could be left set at The last time I pressed one of the load data buttons?)

Mode 2:  Immediate Update

Configure the optimizer to the last time I pressed one of the load data buttons option.  Then when we want to do the update, click on the Load Updated Data button.

Please confirm if the above would accomplish the objectives laid out, or if there is a better / more correct way to do this.  We'd like to automate the overnight process as much as possible.

Thanks and regards,

Paul

Your setting up a manual process here. All you need to do is:-

a:) click on [ Reload All Data ] (once only)

and then run your queries. QODBC will automacically update the optimized file each time you run a query. If you're quering historical data, use the NOSYNC tag to by-pass updating the optimized table. If you're quering new data from today, leave the NOSYNC tag off.

So if my query involves only 1 table, that table will be re-optimized? (or if it involves 3 tables, those 3 tables will be re-optimized)?  Is that correct?

Thanks and regards,

Paul

Hi Tom -

When we run the optimization for the first time ("offline"), we want to know the time it takes to optimize.

If we record the start time when we hit Reload, would the date/time on the OPT file tell us the end time?

Unless we leave the Optimizer Dialog Box checked, how will we know the optimization is complete?

Is there any way (other than watching the screen for 1-3 days)  to know how long the optimization took?