I followed the instructions and I am attempting the queries now. Could the iterator error be related to a corrupt optimizer file? I was getting that error without using the UNOPTIMIZED argument as well.

Also, could a corrupt file explain the BillItemLine NULL error I was receiving?

Most queries are returning this iterator error - it has rendered the application unusable. Any suggestions at all? I have already reinstalled QODBC with no luck.

Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "egroup_QB". OLE DB provider "MSDASQL" for linked server "egroup_QB" returned message - The iteratorID {fef44ce9-bb35-42db-8e20-1ba493576f73} is not valid.

Help.

Ian

You need to be using the Microsoft OLE DB Provider for ODBC for your QODBC linked tables.

MSDASQL is the Microsoft OLE DB Provider for ODBC -

Some queries work and some don't - the same queries that are now failing with the above error worked fine a couple of days ago. Even now, some continue to work.

Is that error being returned from QODBC or the QB SDK?

Please ensure you're running QODBC v6.00.00.176 and then test the queries that fail using VB Demo and look at both the QODBC and SDK Message logs to see what the real errors are.

See: How do I turn on the trace log? for more.

One other thing to note - just as I was having issues with a certain column being NULL in BillItemLine - TimeTracking returns the iterator error when I select certain columns but not when I select * (wierd!).

Example from QODBC Log: (There were a lot of these)

2006-07-10 18:50:12 QODBC Ver:  6.00.00.176 *********************************************************************************************************************
IsAService: True
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

Example from SDK Log:

20060710.185349 I 3988 RequestProcessor ========= Started Connection =========
20060710.185349 I 3988 RequestProcessor Request Processor, QBXMLRP2 v5.0
20060710.185349 I 3988 RequestProcessor Connection opened by app named 'FLEXquarters QODBC'
20060710.185349 I 3988 CertVerifier The file does not contain an Authenticode signature.
20060710.185352 I 3988 RequestProcessor Opening the file in the DoNotCare mode.
20060710.185352 I 3988 RequestProcessor Connection closed by app named 'FLEXquarters QODBC'
20060710.185352 I 3988 RequestProcessor ========== Ended Connection ==========
20060710.185352 I 552 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3988).
20060710.185354 I 552 SpecVersion Current version of qbXML in use: 5.0
20060710.185354 I 552 QBSDKMsgSetHandler QUERY: Bill
20060710.185354 I 552 BillStorage::DoQuery Setting iterator chunk size to 00000100
20060710.185732 I 552 QBSDKMsgSetHandler Request 1 completed successfully.
20060710.185732 I 552 MsgSetHandler Finished.
20060710.185732 I 552 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3988), ret = 0.
20060710.185733 I 552 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3988).
20060710.185734 I 552 SpecVersion Current version of qbXML in use: 5.0
20060710.185734 I 552 QBSDKMsgSetHandler QUERY: Time Tracking
20060710.185734 I 552 TimeTrackingStorage::DoQuery Setting iterator chunk size to 00000100
20060710.185735 I 552 QBSDKMsgSetHandler Request 1 completed successfully.
20060710.185735 I 552 MsgSetHandler Finished.
20060710.185735 I 552 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3988), ret = 0.

Yes, they work fine in VBDemo. Also, the query that returns the null error in BillItemLine works fine in VBDemo.

This was a sudden thing - everything was working great and then these null and iterator errors started. I am not aware of anything that changed to cause this to happen.

Thanks again for your help.

Ian

I also found this in the log:

2006-07-10 19:44:40 QODBC Ver:  6.00.00.176 *********************************************************************************************************************
IsAService: True
SQL Statement: SELECT TxnID, VendorRefListID, TxnDate, DueDate, AmountDue, TermsRefListID, ItemLineCustomerRefListID, ItemLineItemRefListID, ItemLineQuantity, ItemLineCost, ItemLineAmount, ItemLineBillableStatus, Memo, ItemLineDesc FROM BillItemLine WHERE ItemLineI
temRefListID = '370001-1118367865'
Error Getting XML in BuildAndLoadXMLForNextRecord
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="5.0"?>
<QBXML>
    <QBXMLMsgsRq onError="continueOnError">
        <BillQueryRq requestID="1" iterator="Continue" iteratorID="{ae480fd2-f056-4bec-9fc5-ee67185c00ba}">
            <MaxReturned>00000100</MaxReturned>
            <IncludeLineItems>1</IncludeLineItems>
            <IncludeLinkedTxns>0</IncludeLinkedTxns>
            <IncludeRetElement>TxnID</IncludeRetElement>
            <IncludeRetElement>EditSequence</IncludeRetElement>
            <IncludeRetElement>VendorRef</IncludeRetElement>
            <IncludeRetElement>TxnDate</IncludeRetElement>
            <IncludeRetElement>DueDate</IncludeRetElement>
            <IncludeRetElement>AmountDue</IncludeRetElement>
            <IncludeRetElement>TermsRef</IncludeRetElement>
            <IncludeRetElement>Memo</IncludeRetElement>
            <IncludeRetElement>ItemGroupLineRet</IncludeRetElement>
            <IncludeRetElement>ItemLineRet</IncludeRetElement>
        </BillQueryRq>
    </QBXMLMsgsRq>
</QBXML>

Queries like:

SELECT TxnID, VendorRefListID, TxnDate, DueDate, AmountDue, TermsRefListID,
ItemLineCustomerRefListID, ItemLineItemRefListID, ItemLineQuantity,
ItemLineCost, ItemLineAmount, ItemLineBillableStatus, Memo, ItemLineDesc
FROM BillItemLine WHERE ItemLineItemRefListID = '370001-1118367865'

have to do a full table scan of BillItemLine. So the larger the number of records, the longer it's going to take. At this stage I'm guessing something is happening while running this query via a SQL Server Linked table.

Does the error happen all of the time, some of the time, or if it's the only thing running?

What version of QuickBooks are you running (including update release [R6 or R7])?

The null item error happens every time with the BillItemLine. The error that is returned to SQL is not a SQL error, it is a QB error isn't it?

The larger issue is most queries now fail with the iterator error I mentioned to you. I did get a customer query to run yesterday but it only returned 101 rows before failing and giving the iterator error. I am seeing this iterator error in Customer, Vendor, TimeTracking, etc.

Quickbooks Pro 2006 Release R7P

We will most likely add a setting to the QODBC Advanced Setup tab to turn off the Iterators for use with MS SQL Server and other multi-threaded applications. The QuickBooks session is per thread and we have to restart a new session every time the MS SQL Server thread ID changes.

Note: Only USA Editions of QuickBooks 2006 have the iterator feature, meaning QuickBooks 2005 and other country versions (non-USA editions) don't have this issue. 

This has been fixed in QODBC Setup v6 Build 186 (or higher):

QODBC Advanced Setup

The selected defaults in the Advanced settings should apply to most users and normally don't need not be changed.