Ugh!  I'm really struggling here and desperately need some help.  I've been fighting this issue for a couple of weeks now. I'm an IT consultant and am trying to help a client utilize QODBC desktop (read only).  They have QB Enterprise Solutions 6.0.  Although they originally started with version 6.0.0.147 of QODBC, in dealing with the symptoms I'll describe below, I've already upgraded them to 6.0.0.155.

I've been very diligent in going through ALL the FAQs and HelpDesk postings.  I've tried everything I know to try and still have problems.

As a matter of reference, here are the current QODBC settings:

QB is setup to allow QODBC to login automatically and in testing it works fine (except for the gross problem to be explained below).

General Tab:

   Locate a company file is set and the path and file name are correct.

   Data Source Name: QuickBooks Data

   Mode when opening: Multi-user mode

   Test Connection to QuickBooks is SUCCESSFUL!

Messages Tab:

   Detail Tracing (slow) is NOT checked.

   BOTH Display Optimizer Status Panel AND Display Driver Status Panel ARE CHECKED.

Optimizer Tab:

   Use Optimizer is CHECKED.

   Optimizer Database Folder is set correctly (to the Optimizer folder located immediately beneath the QODBC install folder).

   Keep my optimized data up to date as of: has been set to every option possible at one time or another, during this testing.

A couple of side requests: 

1) Where can I get documentation on what all these options mean, in particular the "The last time I pressed one of the load buttons" option, AS WELL AS on just what the Load Updated Data and Reload All Data options actually do?)

2) What is the "Simulate Transaction Support for SQL Server" option on the Advanced tab?  It is currently UNCHECKED.  Does it apply to READ ONLY selects from QB tables?

3) I can speculate what the difference is between QODBCFUL.EXE and QODBCUPD.EXE, but is there any documentation on these programs and any OPTIONS on how to run them?

Here is the scenario I am dealing with:

(NOTE:  I've gone through this same scenario several times, both on version 6.0.0147 AND on 6.0.0.155.  The results are always the same.)

Our overall need is to extract data from two tables only, InvoiceLine and SalesReceiptLine.  At this point in time, we don't need ANY other QB data (may in the future).

As a reference, this client's *.qbw data file is 142 MB in size (146,276 KB as reported by Windows Explorer).  The InvoiceLine table currently has 1,073 records, and SalesReceiptLine has a little over 29,000 records.

Once I got everything setup and working, per some recommendations from Intuit Tech Support, I then ran QODBCFUL.EXE.  It took a good 14-15 HOURS to run to completion.  When finished, the *.OPT file in the Optimizer folder was 180 MB in size (185,228 KB).

Then, I setup a SQL Data Transformation Service (DTS) job that simply pulls all records from both InvoiceLine and SalesReceiptLine into SQL tables (on a Microsoft SQL Server).  The intent here is to then be able to more fully manipulate and manage the data via SQL Server.  This DTS job essentially does a SELECT * FROM InvoiceLine and a SELECT * FROM SalesReceiptLine.

I've read your FAQ postings about applications that deal with ODBC smoothly vs. those that don't.  I think it fair to say that SQL's DTS is very ODBC compliant and generally works VERY WELL with other ODBC drivers.  In 9+ years of dealing with SQL server and ODBC, I have NEVER had results like I'm getting with QODBC (as far as poor performance).

(NOTE:  I've read the QBtoSQLServer.doc Word document on your sight and it does NOT help me. I have no problem setting up SQL DTS jobs just fine.  It's the performance of the QODBC driver that's killing me.)

(Keep in mind I've done this several times, all with the same results.) 

Following the 14+ hour run of QODBCFUL, if I then run my DTS job, it runs in a matter of MINUTES.  In other words, the DTS job successfully copies all InvoiceLine records (1073) and SalesReceiptLine (29,000+) records to SQL in a matter of minutes (less than 10 maybe close to 5 minutes).  However, if I wait a few hours, and then try the DTS job again, it then takes FOREVER (more details below).  Even if nobody has even USED QuickBooks since the last time I ran the DTS job.

For example, after this most recent setup/run of QODBCFUL, I then ran the DTS job successfully (in minutes).  This was about 4 PM yesterday.  Nobody used QB after this.  I then had the DTS job scheduled (NOT VIA SQL SERVER, but as an application on the console) to run at 1 AM this morning.  After 7 hours and 45 minutes, the DTS job had processed all InvoiceLine records (1073) and was only 1300 records into SalesReceiptLine (that's right, 1,300 out of 29,000+).  The same DTS job that ran in minutes before, was now taking almost 8 hours and was barely even started (from a record count perspective).

On a previous occasion (same scenario), we let the job run for 17+ hours, and it was only up to 5000 records in SalesReceiptLine.  Again, after an initial run that only took minutes.  It's as if the 1st run works, but all subsequent do not?!?!?

I've even tried running QODBCUPD.EXE right before running the DTS job at night, but QODBCUPD.EXE then kicks in to taking FOREVER (12+ hours before I stopped it).

I've completely turned off optimization, deleted the *.OPT file, and started all over on several occasions.

In case it helps, here are last several entries from the QODBCLOG.TXT file (I simply copied all items from yesterday at 4:05 PM on down).

(NOTE:  After upgrading to 6.0.0.155, the driver was stating it was a 30 day eval - despite this being a QB Enterprise Solutions 6.0 installation.  Per the instructions in your FAQ forum, I bumped the PC's date to next month and then back, to get over that message.  Now, as you can see, we're getting some odd "30 - CURRENT DATE OLDER" messages.  )

Also, the "...disk image is malformed" message appears all over the place in this file.  I wasn't sure if this was referencing the *.OPT optimization file OR the *.qbw actual QB data file?!?!?  We've run QB's own maintenance and it says everything is fine.  As previously stated, I've tried several distinct attempts at the optimization and still get these same results.

I HIGHLY suspect the item from 1:17 AM this morning is the DTS starting the InvoiceLine export, and the 4:29 AM entry is the DTS job starting the SalesReceiptLine export.  Note that would mean it took OVER 3 hours to export 1,073 InvoiceLine records. :-(

2006-04-19 16:05:30 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: Unknown
Startup Failure Calling Update: 30 - CURRENT DATE OLDER

2006-04-20 01:17:28 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: Unknown
Startup Failure Calling Update: 30 - CURRENT DATE OLDER

2006-04-20 01:17:28 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: Unknown
Startup Failure Calling Update: 30 - CURRENT DATE OLDER

2006-04-20 01:17:51 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: True
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
database disk image is malformed

2006-04-20 04:28:48 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: Unknown
Startup Failure Calling Update: 30 - CURRENT DATE OLDER

2006-04-20 04:28:49 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: Unknown
Startup Failure Calling Update: 30 - CURRENT DATE OLDER

2006-04-20 04:29:07 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: True
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
database disk image is malformed

2006-04-20 09:15:45 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: Unknown
Startup Failure Calling Update: 30 - CURRENT DATE OLDER

2006-04-20 09:15:45 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: Unknown
Startup Failure Calling Update: 30 - CURRENT DATE OLDER

HELP!  I'm on the verge of being suicidal at this point. 

Thanks in advance for any and all help.

 John Neighbors
 Senior Consultant <><
 Professional edge

All the QODBC Setup options (including what each option means and does) is found at: How do I setup QODBC? What are the setup options? 

As far as the optimizer is concerned, you need to decide whether to use it or not. Moving your system date forward and back will corrupt the .opt file. To fix a currupt .opt file see: How do I switch OFF or RESET the QODBC optimizer? 

As your interest is only on two tables here, don't do QODBCFUL or QODBCUPD or any of that stuff. You can override your QODBC Optimizer configuration screen settings and choose how to execute your queries by using the following tags:

VERIFIED | VERIFY - Forces Full Resync with QuickBooks on the optimized table before Query starts
CALLDIRECT | UNOPTIMIZED - Passthru query directly to QuickBooks - use no optimizations
OPTIMIZE | OPTIMIZED | NOSYNC - Passthru query to optimized table directly for maximum speed

Examples:

select * from InvoiceLine UNOPTIMIZED
select * from InvoiceLine NOSYNC
select * from InvoiceLine VERIFIED

To read all the InvoiceLines directly out of QuickBooks use:

select * from InvoiceLine UNOPTIMIZED

Or you can resync your optimized InvoiceLine table by first doing:

sp_optimizefullsync InvoiceLine

then read directly out of the optimized table by doing:

select * from InvoiceLine NOSYNC

This is very f...a.......s.................t

Just use the standard OPENQUERY syntax. An example would look like:

SELECT * FROM OPENQUERY(QODBC, 'sp_optimizefullsync InvoiceLine')

Your client's *.qbw data file is 142 MB in size, this is big in QuickBooks terms. The optimizer is intended as a means to optimize historical data, say transactions from 1995 to 2005 etc., but when it comes to today's payments etc, I always call directly into QuickBooks. That's why we have all the optimizer options .... so you can use it the way that suits the task at hand.