I am querying invoice line from excel and I only get one line per invoice if I use the optimizer. If I do not use the optimizer then I get multiple lines but they're all identical copies of the first invoice line. This happens if I select a year's worth of invoices or a single invoice.  I can run quickbooks and see the correct multiple lines for an invoice but this is not what I see in my query results. For example, I have a 4 line invoice and query InvoiceLine by the ponumber but only get 1 record in the result set; the last line on the invoice.

This is for QuickBooks Pro 2002 with QODBC 6.00.00.155. I am using Excel 2003. The Database is SQL Server 2000 SP4.

The query is simple enough: select * from InvoiceLine where ponumber = '35335'

If I use the optimizer I get the one line. If I change the query to select * from invoiceline unoptimized where ponumber = '35335'  then I get 4 lines but each line is an identical copy of the first line.

Any ideas? Has anyone else seen this happen?

You haven't said what was actually correct, 1 or 4 lines for that invoice? The InvoiceLine shows a row for every line in a invoice, so if a invoice had 4 lines, I would get 4 rows. I checked to see if my results were valid by running both:-

select txnid, txnnumber, refnumber, ponumber, InvoiceLineDesc from InvoiceLine where ponumber is not null

and

select txnid, txnnumber, refnumber, ponumber, InvoiceLineDesc from InvoiceLine unoptimized where ponumber is not null

and I found that the data rows returned matched the actually invoices in QuickBooks.

You can fully resync your optimized InvoiceLine table by running:-

sp_optimizefullsync InvoiceLine

(I tried replying twice before but the post didn't complete)

The actual invoice in the example has 4 lines. When the query was run with optimization only the last line was produced. When I ran the query for all invoices only 1 line per invoice was in the result set.

When I executed the query with unoptimized specified the results showed the first line multiple times instead of showing each line.

I believe the problem was caused by changing the DSN setting to turn off optimization while I was already connected in MSQuery. I exited MSQuery and Excel and the unoptimized query worked correctly after restarting.