Hi,

Note:  I am not sure if the previous post went through. Please disregard this if it did.

Creating an application which gets all Invoices from company 1 and create a bill in company 2.  Both companies in QBOE. Company 1 requires 3 tables using the following requests:

Select    {fn CONVERT(InvoiceLine.TxnDate,SQL_CHAR)}, 
                      {fn CONVERT(InvoiceLine.DueDate,SQL_CHAR)},
                      InvoiceLine.RefNumber,
                      InvoiceLine.InvoiceLineDesc,
                      InvoiceLine.InvoiceLineAmount,
                      CheckExpenseLine.ExpenseLineAccountRefFullName
                      FROM InvoiceLine, CheckExpenseLine
                      WHERE InvoiceLine.CustomerRefListID = '210'
                      AND InvoiceLine.CustomerRefListID = CheckExpenseLine.ExpenseLineCustomerRefLIstID
                      AND InvoiceLine.InvoiceLineServiceDate = CheckExpenseLine.TxnDate
                      AND InvoiceLine.InvoiceLineAmount = CheckExpenseLine.ExpenseLineAmount
                      and {fn TIMESTAMPDIFF(SQL_TSI_MONTH,InvoiceLine.TimeCreated,{fn NOW()})} = 2 Order by InvoiceLine.TimeCreated

A similar query for BillExpenseLine.

To make things faster I do an SP_OptimizeFullSync for each Table before I issue the select statements. Somehow after posting the bills, some records are not entered causing invoices and bills not to balance.   I also noticed that  When OptimizeFullSync is done for CheckExpenseLine, it only loads 2600+ out of the 3,500+ records. 

I have tried to do an Unoptimized but got lesser records, tried the same select with your {OJ and got lesser records as well.

Please help.

Please download and install QODBC v6.00.00.176 (see How can I get the latest version of QODBC? ), then retry and advise if there's any difference in the number of rowsets returned doing each of these select statements:

Select * from CheckExpenseLine
Select TxnID from CheckExpenseLine
Select * from CheckExpenseLine UNOPTIMIZED
Select TxnID from CheckExpenseLine UNOPTIMIZED

All 4 selects came out 2209/3000+  records.  Only records from May, 05 thru now were received.  When I tried to sort using Order by ---- an error comes out about the 1000 limitation.  Only way I got the others is if I do the following:

SELECT * FROM CHECKEXPENSELINE UNoptimized where TxnDate < {d'2005-06-01'}

On the other hand, my application got more records.   A little bit more accurate when UNOPTIMIZED in billexpense and checkexpense. I am running backwards... May, April and March and on. So far so good. I just hope everything can be optimized.   Is there a way to load the first half into Optimizer and do an update to load the second half? Maybe a limiter in SP_Optimize....

I suggest you reset your optimized tables, see How do I switch OFF or RESET the QODBC optimizer? and then resync your optimized CHECKEXPENSELINE table by running:-

sp_optimizefullsync CHECKEXPENSELINE

The QuickBooks Online Edition limits the number of rows returned by a query to 1,000, so you will need to break large queries into smaller query sets like this:

SELECT * FROM CHECKEXPENSELINE where TxnDate > {d'2003-06-30'} and TxnDate < {d'2004-07-01'}
SELECT * FROM CHECKEXPENSELINE where TxnDate > {d'2004-06-30'} and TxnDate < {d'2005-07-01'}
SELECT * FROM CHECKEXPENSELINE where TxnDate > {d'2005-06-30'} and TxnDate < {d'2006-07-01'}

Once the data is loaded in the optimized table, you can then do larger queries doing:

SELECT * FROM CHECKEXPENSELINE NOSYNC where TxnDate < {d'2005-06-01'}

Records start from 2005-01-03.

Select * From CheckExpenseLine where TxnDate > {d'2005-01-01'} and Txndate < {d'2005-06-01'}

Returns 13 records

Unoptimized returns 500+ records

I turned on Status for Optimizer and Driver and noticed something.

When Optimized (first select), Optimizer shows 2187 records, Driver shows 2187 of 2187 records, then it pauses then it tries the do an xml parse again and total records goes to  4374 and  records read jumps to 2188 then goes back to 2187.  All status closes.

It looks like you will need to limit your queries to less than 1,000 records returned for each query using the UNOPTIMIZED tag.