Author Message DPantelic Group:Members Posts:1 Joined:2008-08-19 Profile Posted:2008-08-19 02:38:54

I am using the QODBC Driver to connect to QuickBooks Online Edition and get the data on the payments made from the last time I ran the program.
I am trying to run the next SQL statement: "SELECT * FROM ReceivePayment WHERE TxnNumber > 20530". When I run this statement in the QODBC Online Support Wizard program, I get the table headers of the ReceivePayment table and no data. I checked the Review QODBC Messages and got this message:

2008-08-18 11:50:39 QODBC Ver: 8.00.00.243 *********************************************************************************************************************
IsAService: False
SQL Statement: SELECT * FROM "ReceivePayment" CALLDIRECT
Error Getting XML in BuildAndLoadXMLForNextRecord
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE QBXML PUBLIC '-//INTUIT//DTD QBXML QBO 6.0//EN' 'http://apps.quickbooks.com/dtds/qbxmlops60.dtd'>
<QBXML>
<SignonMsgsRq>
<SignonTicketRq requestID="0">
<ClientDateTime>2008-08-18T11:50:09</ClientDateTime>
<SessionTicket>V1-87-Iig3_Y2C9A7fijd1sLZNfw:127257129</SessionTicket>
<Language>English</Language>
<AppID>71271511</AppID>
<AppVer>1.0</AppVer>
</SignonTicketRq>
</SignonMsgsRq>
<QBXMLMsgsRq onError="continueOnError">
<ReceivePaymentQueryRq requestID="1">
<MaxReturned>00000500</MaxReturned>
</ReceivePaymentQueryRq>
</QBXMLMsgsRq>
</QBXML>

I also tried running the SQL statement with the CallDirectly option, with the optimizer turned off, and got the same message with just the SQL statement line in the message changed.

Is there a way to get the data that I need?
Tom Group:Administrator Posts:5510 Joined:2006-02-17 Profile Posted:2008-08-19 07:49:51

When any given query could return over 1000 records by the QuickBooks Online Edition (QBOE), the query gets terminated by QBOE at the Intuit end. Assuming that you have been looking at payments since January this year, you need to query a smaller subset of records like this:

Select * from ReceivePayment UNOPTIMIZED WHERE Txndate > {d '2008-01-01'}

Where the date format is {d 'YYYY-MM-DD'}

If it works, you can remove the UNOPTIMIZED (or CALDIRECT) tag, as I've only added it to bypass your local optimized tables in case there's something wrong. It would help if you always used the QODBC Optimizer with using QBOE.

post_id 2580 forum7c0b.html Link to Original Article

Tags: QuickBooks Online, QBO, QODBC Online, Receive payment, More than 1000 records