| Can't Return All Invoices from QuickBooks Online Edition using VBDemo 32 |
| Author |
Message |
|
|
| Posted : 2006-05-27 02:14:55 |
We are developing an application to import new invoices into the Online Edition of Quickbooks.
I'm needing to see which fields we are using to map data to the fields in QB. When using the VBDemo 32 app, I can't seem to get ANY Invoices when I run
SELECT * from Invoice
SELECT * from InvoiceLine
Looking in our company file, we've got thousands of Invoices....why am I not able to see these?
I'm able to run other queries successfully, like returning all customers, etc...
Thanks for any help. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-05-27 09:49:26 |
When over 1000 records could be returned by any given query to the QuickBooks Online Edition, the query get's terminated by QBOE at the Intuit end. Assuming that you have entered invoices since May this year, you need to query a smaller subset of records like this:
select * from InvoiceLine UNOPTIMIZED WHERE Txndate > {d '2006-04-30'}
Where the date format is {d 'YYYY-MM-DD'}
You can also remove the UNOPTIMIZED tag if it works as I've only added it to bypass your local optimized invoice and invoiceline tables in case there's something wrong there. You should always use the QODBC Optimizer when using QBOE. |
|
|
|
|
|
| Posted : 2009-01-30 09:17:03 |
Hi Tom,
I'm having a similar issue here, even when limiting my data to much fewer than 1000 rows (these other posts are relatively old so maybe something is different now... and I'm currently using version 9).
In any case, it appears that a connection to QBOE via the driver only lasts up to 30 seconds and if it's not returning results by then the connection is terminated regardless of the number or rows.
Can you please verify and let me know if there are any options here. This could be a major issue for me.
Thanks,
Dave |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-01-30 09:39:29 |
Doesn't sound like you're using VB Demo to me. VB Demo doesn't disconnect until you close the connection or close VB Demo. The 1000 record limit still applies to QBOE. Try something really simple:
select * from Invoice UNOPTIMIZED WHERE Txndate > {d '2009-01-29'} and Txndate < {d '2009-02-01'}
or
select * from host |
|
|
|
|
|
| Posted : 2009-01-30 10:34:04 |
I am definitely using VB Demo. However, it's not that VB Demo disconnects, it's just that no results are returned at all. I have the QODBC Driver Status panel enabled and it comes up, counts to 30 (seems to almost always be exactly 30, but I may have seen it get to 32 once) with the screen that says "Waiting On QuickBooks", then just disappears without delivering any results (without the panel, the hourglass simply goes away). If (with a more limited query) it gets to the next step where it does the Initial XML Parse before 30 seconds, all is fine (and the timer goes proceeds above 30).
So, I can certainly limit the results with something similar to your example queries for the purposes of just getting some results in VB Demo, but there may be times (I use the driver within another program where results will vary) that I need the query to last more than 30 seconds and possibly deliver a few hundred results. A good example of a query that returns nothing but is less than 1000 rows is:
select top 500 * from invoiceline
but
select top 10 * from invoiceline works fine
(optimizer is off)
Please let me know if you can reproduce this problem and/or have any ideas of what is going on here.
Dave |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-01-30 11:49:36 |
| Top doesn't really limit the returned rows, it's actually a filter for the rows to be displayed. You need to limit your data into subsets by using a WHERE clause. |
|
|
|
|
|
| Posted : 2009-01-31 04:49:25 |
I am only using TOP here as an example to make it easy to explain, the same thing happens using just a WHERE. Let me try to give a concrete example.
If I run the query:
select * from invoiceline where txndate > {d '2009-01-24'} and txndate < {d '2009-01-30'}
I get 442 results... all is fine,
but when I run:
select * from invoiceline where txndate > {d '2009-01-23'} and txndate < {d '2009-01-30'}
i.e. just one more day, it just stops after 30 seconds and I get no results.
If the limit were simply 1000 rows, I would need to have an additional 558 rows on 2009-01-23 for that to make sense, but I don't since:
select * from invoiceline where txndate = {d '2009-01-23'}
gives just 121 results.
To make matters worse, this "threshold" above can and does change (sometimes using " > {d '2009-01-24' " doesn't return results but the 25th does) ... possibly dependent on internet traffic or something else that affects how fast it can process.
My conclusion is that there is some built in timeout here before it ever gets to 1000 rows. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-01 11:14:12 |
So what happens if you do?:
select * from invoiceline unoptimized where txndate > {d '2009-01-23'} and txndate < {d '2009-01-30'}
|
|
|
|
|
|
| Posted : 2009-02-03 11:16:33 |
That makes no difference. I've got the Optimizer off in any case.
Dave |
|
|
|
|
|
| Posted : 2009-02-14 03:43:55 |
Any response to this? This is still a major issue for me... sometimes I get results, and sometimes I don't. In any case the cut-off is always at 30 seconds. Sometimes I have to query (on a very limited query) several times to get the results I need and it's very time consuming.
Dave |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2009-02-15 12:42:54 |
|
|
|
